XData and PostgreSQL Text Field

BIZ TMS XData
1

i have this entity in my server:

  [Entity]
  [Table('bn_test_table')]
  [Id('FIdTestTable', TIdGenerator.Guid)]
  TBnTestTable = class
  private
    [Column('id_test_table', [TColumnProp.Required])]
    FIdTestTable: TGuid;
    
    [Column('json_field', [TColumnProp.Lazy])]
    [DBTypeMemo]
    FJsonField: TBlob;
  public
    property IdTestTable: TGuid read FIdTestTable write FIdTestTable;
    property JsonField: TBlob read FJsonField write FJsonField;
  end;

i need to save and get a json string in the "JsonField" property. First i'm trying to get the json value in the field with xdata but i didn't have success.
Here's my table and the record:

image
image673×429 13.3 KB

i tried catch this that way

http://localhost:2001/tms/xdata/BnTestTable?expand=JsonField

and the server responded with this:

{
    "value": [
        {
            "$id": 1,
            "IdTestTable": "C76B1897-BA78-9F45-94CB-8F9A8C7528F8",
            "JsonField@xdata.proxy": "BnTestTable(C76B1897-BA78-9F45-94CB-8F9A8C7528F8)/JsonField"
        }
    ]
}

Is this the correct way to save and get json value with XData and, if it is what's wrong with my code?

2

You should use $expand=JsonField - you missed the $ character.

3

Changing the character, i received a different response. This time instead of "JsonField@xdata.proxy"
i received a string that looks like a JWT or something like that, not the json stored on the database:

[
	{
		"IdTestTable": "C76B1897-BA78-9F45-94CB-8F9A8C7528F8",
		"JsonField": "ewAiAHQAaQBjAGsAZQB0ACIAOgB7ACIAaQBkACIAOgAxADkANQA1ADcAMQAyADcALAAiAHMAdABvAHIAZQBfAGkAZAAiADoAMQA0ADAALAAiAGgAZQBhAGQAcQB1AGEAcgB0AGUAcgBfAGkAZAAiADoAbgB1AGwAbAAsACIAZQB4AHQAZQByAG4AYQBsAF8AcwB0AG8AcgBlAF8AaQBkACIAOgBuAHUAbABsACwAIgB0AG8AdABhAGwAXwBwAHIAaQBjAGUAIgA6ADYALgA1ACwAIgBpAG4AdABhAGsAZQBkAF8AYQB0ACIAOgAiADIAMAAyADMALQAwADIALQAwADEAIAAxADMAOgA1ADgAOgA1ADYAIAAtADAAMwAwADAAIgAsACIAYwByAGUAYQB0AGUAZABfAGEAdAAiADoAIgAyADAAMgAzAC0AMAAyAC0AMAAxACAAMQAzADoANQA5ADoAMAA3ACAALQAwADMAMAAwACIALAAiAHIAZQBmAHUAbgBkAGUAZABfAGEAdAAiADoAbgB1AGwAbAAsACIAdAB5AHAAZQAiADoAIgBEACIALAAiAHMAdAB1AGQAZQBuAHQAIgA6AHsAIgBpAGQAIgA6ADEANQA2ADMANwAzACwAIgBuAGEAbQBlACIAOgAiAEcAQQBFAEwAIABHAFIASQBNAEIARQBSAEcAIABaAEUARwBNAEEATgAiACwAIgByAGUAZwBpAHMAdAByAGEAdABpAG8AbgAiADoAIgA0AGMANQBhAGUAZQAyADYAOQAxADAANABhAGYAZAA4ADgAOABjADUANgBhAGIAOQBjADYAYQA2AGUAYwBkADQAIgAsACIAZQB4AHQAZQByAG4AYQBsAF8AYwBvAGQAZQAiADoAbgB1AGwAbAAsACIAcgBlAHMAcABvAG4AcwBpAGIAbABlAF8AYwBwAGYAIgA6ACIAMAA0ADEANAA2ADcAMAA4ADQAMQA5ACIAfQAsACIAcAByAG8AZAB1AGMAdABzACIAOgBbAHsAIgBpAGQAIgA6ADIANwAxADMAMQAyADkANgAsACIAcwB0AG8AcgBlAF8AcAByAG8AZAB1AGMAdABfAGkAZAAiADoANAAxADQAMQA5ACwAIgBjAG8AZABlACIAOgAiACIALAAiAHEAdQBhAG4AdABpAHQAeQAiADoAMQAsACIAcwBlAGwAbABpAG4AZwBfAHAAcgBpAGMAZQAiADoANgAuADUALAAiAGQAZQBzAGMAcgBpAHAAdABpAG8AbgAiADoAIgBDAGgA4QAgAGYAZQBlAGwAIABnAG8AbwBkACAAYgByAGEAbgBjAG8AIABsAGkAYwBoAGkAYQAgADMAMwAwAG0AbAAgACgAZgBlAGUAbAAgAGcAbwBvAGQAKQAiACwAIgBzAGgAbwByAHQAXwBkAGUAcwBjAHIAaQBwAHQAaQBvAG4AIgA6ACIAQwBoAOEAIABmAGUAZQBsACAAZwBvAG8AZAAgAGIAcgBhAG4AYwBvACAAbABpAGMAaABpAGEAIAAzADMAMABtAGwAIgAsACIAdwBlAGkAZwBoAHQAIgA6ACIAMwAzADAAbQBsACIAfQBdAH0AfQA="
	}
]

how can i solve it?

4

That is the text content encoded in Base64. You should get that value and pass it through a Base64 decoder to have your final text.

If you don't want or don't need to have your json_field to be lazy-loaded, you can simply map it as a string type. Just make the size 65536 and then it will still be a blob in your database (a text blob, actually) and in XData it will appear inline as raw text:

   [Column('json_field', [], 65536)]
   FJsonField: string;
5

Thank's for the help, this worked fine for me.
Do you think this is the best way to use store and get JSON on PostgreSQL, or there is another way to make this?

6

It's fine that way.

7

Again, thank's for the help!