Convert a JSON object

sigrid1 — Thu, 08 Jun 2023 13:16:56 GMT

Hello,

is it possible to transform a json object into columns? I found the following solution, but this works only if all the values are strings. If I've subproperties or numeric fields it doesn'work.

For example if the json object look like this for source Product,width,amount,height,length the values are empty.

Does it exist a easy solution to transform json field into columns?

Thanks

Sigrid

{"posNumber": "181020212034",
"plugedSites": 2,
"sourceProduct": {
"width": 1200,
"amount": 20,
"height": 42,
"length": 5000,
"quality": "B/",
"woodtype": "HF1",
"productId": "00438"}
}

[BOOKINGS]:
LOAD
RecNo() as RecId,
id as Field1,
timestamp as Field2,
properties as Field3;
SQL
select id,timestamp,properties
from production.bookings;

Attributes:
Generic
LOAD
RecId,
TextBetween(Attribute, '"', '"'),
TextBetween(Value, '"', '"')
;
LOAD
RecId,
SubField(pair, ':', 1) as Attribute,
SubField(pair, ':', 2) as Value
;
LOAD
RecId,
SubField(raw, ',') as pair
;
LOAD
RecNo() as RecId, // Id used to stitch rows back together
TextBetween(Field3, '{', '}') as raw
Resident [BOOKINGS];

topic Convert a JSON object in App Development

Convert a JSON object