Unpack json-like string

[_Johan]

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

[_Johan · Accepted solution]

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.