Unpack json-like string

_Johan — Thu, 31 Oct 2024 11:11:54 GMT

Hi,

I have a json-like string in a field in a loaded table. As you can see it has a lot of whitespace and new line characters in it which probably doesn't help. It also differ a bit between what is in the different sections {}

[{ "UID": 1, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": false, "IsHeader": true, "IsOpenInventory": false, "isCalculatedActual": false },{ "UID": 10, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": true, "isCalculatedActual": false, "StoragePointUID": 309,"ValuePlanned": 0,"Value": 0},{ "UID": 2, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": true, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "EFC1", "ChannelType": "Storage", "ChannelUID": 753, "IOChannel": "Incoming"},{ "UID": 3, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": true, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "EFC2", "ChannelType": "Storage", "ChannelUID": 761, "IOChannel": "Incoming"},{ "UID": 4, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "Local", "ChannelType": "Manual", "ChannelUID": 755, "IOChannel": "Outgoing"},{ "UID": 5, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "", "ChannelType": "Manual", "ChannelUID": 763, "IOChannel": "Outgoing"},{ "UID": 6, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "Export", "ChannelType": "Manual", "ChannelUID": 765, "IOChannel": "Outgoing"},{ "UID": 7, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": true, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "Off spec", "ChannelType": "Storage", "ChannelUID": 773, "IOChannel": "Outgoing"},{ "UID": 8, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": true, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "Damietta", "ChannelType": "Storage", "ChannelUID": 779, "IOChannel": "Outgoing"},{ "UID": 9, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": true, "isEndInventory": false, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309, "ChannelName": "Adabiya", "ChannelType": "Storage", "ChannelUID": 907, "IOChannel": "Outgoing","ValueActual": 5000.00000000},{ "UID": 11, "Date": "Jun 23, 2021", "isActiveChannel": true, "IsConnected": false, "isEndInventory": true, "IsHeader": false, "IsOpenInventory": false, "isCalculatedActual": false, "StoragePointUID": 309,"ValuePlanned": -5000.00000000}]

I found the post with From_Field and have tried it out with some different twists. They all kind of ends up in either of two ways, either there is no data in fields or I get a lot of synthetic keys and the load fails.

My last try was to split up the string into sections like this:

Mere_Json: Load [Index], PurgeChar(Subfield(Replace([Json], '},{', '},,,,{'), ',,,,'), '[]') as StringJson Resident TableWithJson ; drop table TableWithJson;

and then try to use From_Field

json_days: load * From_Field (Mere_Json, StringJson)(json, utf8, no labels); STORE json_days INTO [:MyJsonList.qvd](qvd);

It doesn't go well.

Do any of you have any great ideas how this might be done?
Can I temporarily turn of the synthetic key creation part?
Am I doing anything obviously wrong?

Should this somehow be solved with a inline load since I kind of have the structure for the string?

Kind regards
Johan

Re: Unpack json-like string

_Johan — Thu, 14 Nov 2024 12:59:39 GMT

After some trials and testing I have found a way which unpacks the data into a table.
If I concatenate the load with From_Field all syntetic keys are avoided and the unpacking is quite fast, comparably at least.

Code:

NoConcatenate Mere_Json: Load Json Resident TableWithJson; drop table TableWithJson; New_Json: load * inline [Date]; Concatenate Load * From_Field (Mere_Json, Json)(json, utf8, no labels);

An interesting part here is that I only provide one column name, "Date", in the table and still all the rest are filled in properly.

topic Unpack json-like string in App Development

Unpack json-like string

Re: Unpack json-like string