How to sp_parse_json a row variable when name of an element is unknown?

[cigaras]

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.

[Vlad]

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.

[VolkerBarth · Accepted solution]

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;

[cigaras · Accepted solution]

This works if I simply replace first and last curly brackets with square brackets in my sample JSON... Thank you.

[VolkerBarth]

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.

[cigaras]

That is the issue, JSON is provided by a third party, and I do not know names of fruits in advance.