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.

asked 03 Oct, 02:45

Valdas's gravatar image

Valdas
381141829
accept rate: 83%

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.

(03 Oct, 08:29) Vlad

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;
permanent link

answered 05 Oct, 03:28

Volker%20Barth's gravatar image

Volker Barth
39.1k353534804
accept rate: 34%

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

(11 Oct, 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.

permanent link

answered 05 Oct, 02:56

Volker%20Barth's gravatar image

Volker Barth
39.1k353534804
accept rate: 34%

edited 05 Oct, 02:57

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

(11 Oct, 02:59) Valdas
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×26

question asked: 03 Oct, 02:45

question was seen: 157 times

last updated: 11 Oct, 03:04