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  |
Duane
Messages: 532
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 #689301 is a reply to message #689300] |
Thu, 16 November 2023 09:03  |
Duane
Messages: 532
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}]}
]
}
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 |
Duane
Messages: 532
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 |
 |
mathguy
Messages: 90
Registered: January 2023
|
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 |
Duane
Messages: 532
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 |
|
mathguy
Messages: 90
Registered: January 2023
|
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;
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
{
"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 |
Duane
Messages: 532
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.
|
|
|
|
Goto Forum:
Current Time: Fri Dec 08 00:07:15 CST 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
