I have a JSON string: { "apple": { "amount": 8, "name": "apple", "price": 1 }, "pear": { "amount": 4, "name": "pear", "price": 2 } } How do I parse it so that I get output like this: | name | price | amount | | apple | 1 | 8 | | pear | 2 | 4 | Including any other fruit added into JSON later on. |
FWIW, here's a solution for an array of fruits: begin declare json_data long varchar = '[ { "amount": 8, "name": "apple", "price": 1 }, { "amount": 4, "name": "pear", "price": 2 }, { "amount": 1, "name": "grapefruit", "price": 4 } ]'; drop variable if exists MyVar; create variable MyVar array of row( amount bigint, name varchar(32), price bigint); call sp_parse_json('MyVar', json_data); select MyVar[[n.row_num]].name as "name", MyVar[[n.row_num]].price as price, MyVar[[n.row_num]].amount as amount from sa_rowgenerator(1, cardinality(MyVar)) as n; end; This works if I simply replace first and last curly brackets with square brackets in my sample JSON... Thank you.
(11 Oct '22, 03:04)
Valdas
|
As to the JSON string: If this is a variable list of fruits, wouldn't it be more reasonable to use an array of objects instead of an object consisting of several sub-objects? The use of objects instead of arrays makes sa_parse_json use a row type for each fruit, and as such, you have to access its members by named fields instead of an index. begin declare json_data long varchar = '{ "apple": { "amount": 8, "name": "apple", "price": 1 }, "pear": { "amount": 4, "name": "pear", "price": 2 } }'; call sp_parse_json('myVar', json_data); select (myVar.apple).name as "name", (myVar.apple).price as price, (myVar.apple).amount as amount union all select (myVar.pear).name, (myVar.pear).price, (myVar.pear).amount end; returns the expected result set - but would obviously not work without adaptions for any other fruits. That is the issue, JSON is provided by a third party, and I do not know names of fruits in advance.
(11 Oct '22, 02:59)
Valdas
|
Why cannot you develop a custom extension and load it into SA dynamically? No, I don't know the answer, but I see from your example that JSON schema is not fixed, and I don't expect that sp_parse_json is able to parse whatever_you_have.json. It has own rules, and either you follow them, or develop something custom.