Home » SQL & PL/SQL » SQL & PL/SQL » Help with query returning JSON (Oracle, 19.0.0.0.0, Linux)
Help with query returning JSON [message #689299] Thu, 16 November 2023 07:45 Go to next message
Duane
Messages: 553
Registered: December 2002
Senior Member
I need a bit of help here.  I'm almost there but need some help in figuring out the ROWS.

I'm just trying to return data in this format so that I can understand how to formulate the query for production data.  I'm using example data from Google Charts.


Sample Google Chart Data


{"cols":[{"label":"Topping","type":"string"},{"label":"Slices","type":"number"}],"rows":[{"c":[{"v":"Mushrooms"},{"v":3}]},{"c":[{"v":"Onions"},{"v":1}]}]}


My attempt at producing that JSON data


select json_object ('cols' value (select json_arrayagg (
                                         json_object ('label' value label,
                                                      'type'  value type) null on null returning clob)
                                    from (select 'Topping'  label,
                                                 'string' type
                                            from dual
                                            
                                          union all
                                          
                                          select 'Slices' label,
                                                 'number' type
                                            from dual)),
                    'rows' value (select json_arrayagg (
                                         json_object ('c' value (select json_arrayagg (
                                                                        json_object ('v' value v,
                                                                                     'v' value f))
                                                                   from dual)) null on null returning clob)
                                    from (select 'Mushrooms' v,
                                                  3          f
                                            from dual
                                                                           
                                          union all
                                                                         
                                          select 'Onions' v,
                                                  1       f
                                            from dual)) null on null returning clob) json
     
  from dual;


They above query produces this.  The ROWS are close but not quite in the same format.


{"cols":[{"label":"Topping","type":"string"},{"label":"Slices","type":"number"}],"rows":[{"c":[{"v":"Mushrooms","v":3}]},{"c":[{"v":"Onions","v":1}]}]}


Any ideas on what else I need in the ROWS query to return the data exactly like the Google Charts data?
Re: Help with query returning JSON [message #689300 is a reply to message #689299] Thu, 16 November 2023 08:37 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
I don't know what Google Charts means, but if this is a question about Oracle SQL and about JSON, I don't quite understand the structure of the "desired" output. Specifically, each row is modeled in the JSON as an array: [{"v":"Mushrooms"},{"v":3}] Why? Values in the two columns of a row (the first value being a string like "Mushrooms" and the second a number like 3) do NOT form an array. They DO form an object, with two "attributes", v and f. Why is the f changed to v in the "desired" output?

The proper JSON representation of a row should look like this: {"v":"Mushrooms","f":3} You may consider other formats if needed, but I can't see any justification for turning that into an array.

Re: Help with query returning JSON [message #689301 is a reply to message #689300] Thu, 16 November 2023 09:03 Go to previous messageGo to next message
Duane
Messages: 553
Registered: December 2002
Senior Member
Google Charts

https://developers.google.com/chart


Google Data Example

https://developers.google.com/chart/interactive/docs/php_example


sampleData.json File

A JSON version of a small DataTable.

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

It would appear when I copied the "json_object ('v' value v, 'v' value f)" I didn't change the second "v" to a "f".

With that said, the data still needs to match the Google Data Example, which is, {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]} and not {"v":"Mushrooms","f":3}

I'm just trying to follow what Google is providing as a data example.  Not sure why I would want to change the format from what they are providing.
Re: Help with query returning JSON [message #689302 is a reply to message #689301] Thu, 16 November 2023 09:08 Go to previous messageGo to next message
Duane
Messages: 553
Registered: December 2002
Senior Member
In my first post, it appears I copied in the wrong Google Charts Example Data.  I think that was one of my versions.

Correct Google Chart Data Example


{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

Re: Help with query returning JSON [message #689303 is a reply to message #689302] Thu, 16 November 2023 09:28 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
A quick look at the link to Google Chart shows that you are trying to create the JSON from the wrong base table.

In the "rows" representation, the properties v and f are the "value" and a "formatted string representation of that value", respectively. They pertain to THE SAME COLUMN in the base table. In your base table, the column names should be Topping (as in "Mushrooms") and Slices (as in 3). Then the v and f values are calculated from the table. (It seems that "formatted string representation" would not apply to values that are strings already, or numbers; indeed, these are native JSON data types, so they don't need to be "represented" in any particular way, other than themselves.)

Given the required encoding, I now understand why they use JSON arrays to encode rows. In a more common JSON encoding, the column names would be used as keys in objects representing the rows. In the Google Charts encoding, I guess the column names are treated as variables, not set in stone as is required in SQL. So they have to encode row values in objects with keys v and f (as explained), without knowledge of which value corresponds to which column. So they must be ordered - something that a JSON array does, but a JSON object does not. Since we need to know which value is first, which is second, etc., because the values don't come with their corresponding column name as a key, the values MUST be in an array.

I'll give it a try - I'm not sure I will spend too much time figuring out how Google Chart decides the data type of columns though (so that, for example, it would know whether the f value is needed or it can be left as NULL, as they do in the example).

Re: Help with query returning JSON [message #689304 is a reply to message #689303] Thu, 16 November 2023 09:38 Go to previous messageGo to next message
Duane
Messages: 553
Registered: December 2002
Senior Member
Thank you.

I see where I got my first Google Chart Data Example.  It looks like I was playing around with what worked and what didn't work.

This does work.  It's different from the sample on Google Charts but does produce a pie chart.


{"cols":[{"label":"Topping","type":"string"},{"label":"Slices","type":"number"}],"rows":[{"c":[{"v":"Mushrooms"},{"v":3}]},{"c":[{"v":"Onions"},{"v":1}]}]}

Re: Help with query returning JSON [message #689306 is a reply to message #689304] Thu, 16 November 2023 10:40 Go to previous messageGo to next message
mathguy
Messages: 105
Registered: January 2023
Senior Member
Generating that kind of JSON from an Oracle table will require some hard-coding. The values Mushroom and 3 come from columns names Topping and Slices in the base table. To retrieve these values and place them in the JSON, Oracle requires that the column names be hard-coded in the query. That isn't needed for the "cols" part of the JSON (retrieving the column names and data types - that can be done with a query against *_TAB_COLUMNS, but that requires hard-coding the table name and possibly the owner, unless the query is run by the table owner and the query uses USER_TAB_COLUMNS). But it is needed for generating the "rows" part.

Something like this, perhaps. Note that I kept the f keys - perhaps you will need them for dates and such. In the "cols" value, you can't have the "id":"" key-value pairs; Oracle is specifically known not to distinguish between NULL and empty string, and it does not support the generation of empty string as a JSON value. Well-known issue.

Setting up the base table:

create table pizza ("Topping" varchar2(20), "Slices" number);
insert into pizza ("Topping", "Slices")
  select 'Mushrooms', 3 from dual union all
  select 'Onions'   , 1 from dual union all
  select 'Olives'   , 1 from dual union all
  select 'Zucchini' , 1 from dual union all
  select 'Pepperoni', 2 from dual
;
commit;
Query to generate the JSON string:

select json_object(
         'cols' value (
           select json_arrayagg(
                    json_object(
                      'label' value column_name,
                      'type'  value case when data_type like 'VARCHAR2%' then 'string'
                                         when data_type like 'NUMBER%'   then 'number' end
                    )
                  )
           from   user_tab_columns
           where  table_name='PIZZA'
           )
         ,
         'rows' value (
           select json_arrayagg(
                    json_object('c' value
                      json_array(json_object('v' value "Topping", 'f' value null),
                                 json_object('v' value "Slices" , 'f' value null)
                      )
                    )
                  )
           from   pizza
           )
       )
from   dual
The output (in a single line if run exactly as written; I formatted it to make it readable - not part of the query since that isn't really needed in practice, other than for debugging):

{
  "cols" :
  [
    {
      "label" : "Topping",
      "type" : "string"
    },
    {
      "label" : "Slices",
      "type" : "number"
    }
  ],
  "rows" :
  [
    {
      "c" :
      [
        {
          "v" : "Mushrooms",
          "f" : null
        },
        {
          "v" : 3,
          "f" : null
        }
      ]
    },
    {
      "c" :
      [
        {
          "v" : "Onions",
          "f" : null
        },
        {
          "v" : 1,
          "f" : null
        }
      ]
    },
    {
      "c" :
      [
        {
          "v" : "Olives",
          "f" : null
        },
        {
          "v" : 1,
          "f" : null
        }
      ]
    },
    {
      "c" :
      [
        {
          "v" : "Zucchini",
          "f" : null
        },
        {
          "v" : 1,
          "f" : null
        }
      ]
    },
    {
      "c" :
      [
        {
          "v" : "Pepperoni",
          "f" : null
        },
        {
          "v" : 2,
          "f" : null
        }
      ]
    }
  ]
}
Re: Help with query returning JSON [message #689307 is a reply to message #689306] Thu, 16 November 2023 11:01 Go to previous message
Duane
Messages: 553
Registered: December 2002
Senior Member
Thank you so much.  Just what I needed.

I see I was missing this part.  That's what I needed to put it all together.


json_array(json_object('v' value "Topping", 'f' value null),
           json_object('v' value "Slices" , 'f' value null)


Thanks again.
Previous Topic: Recursive subquery factoring
Next Topic: PL/SQL wrapper error
Goto Forum:
  


Current Time: Tue Feb 27 20:22:45 CST 2024