英文:
Format JSON in Oracle (List Keys Once Followed By Values Sets)?
问题
Sure, here's the translated content you requested:
{"keys": ["VAL1", "VAL2", "VAL3"], "values": [["1", "2", "3"], ["a", "b", "c"], ["1", "b", "3"]]}
You can also access the fiddle for reference.
英文:
I'm looking to see if it is possible to format JSON in Oracle as a listing of the headers once followed by the listing of all the values sets.
Creating the following (on Oracle 19c):
CREATE TABLE tbl1 (val1 varchar2(10), val2 varchar2(10), val3 varchar2(10));
INSERT INTO tbl1 VALUES ('1','2','3');
INSERT INTO tbl1 VALUES ('a','b','c');
INSERT INTO tbl1 VALUES ('1','b','3');
Running this query gives:
SELECT JSON_ARRAYAGG(JSON_OBJECT (*) returning varchar2) AS JSON_VAR
FROM tbl1;
| JSON_VAR |
|---|
| [{"VAL1":"1","VAL2":"2","VAL3":"3"},{"VAL1":"a","VAL2":"b","VAL3":"c"},{"VAL1":"1","VAL2":"b","VAL3":"3"}] |
Is it possible to format the output where we list the keys once then all the sets of values? For example:
{"keys" : ["VAL1", "VAL2", "VAL3"], "values" : [["1", "2", "3"],["a", "b", "c"],["1","b","c"]]}
答案1
得分: 1
select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3))) as js
from tbl1
英文:
select json_object(
'keys' : ['VAL1', 'VAL2', 'VAL3'],
'values' : json_arrayagg(json_array(val1, val2, val3))) as js
from tbl1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论