|
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.