Bring two json objects into one using oracle sqlplus

Question

I have the below query:

SELECT json_object('time' VALUE localtimestamp) || ' '||
json_object('db_schema' VALUE 'USER1') || ' '||
json_objectagg(JOB_STAT value count(ID) returning varchar2(32000))
FROM <TABLE> GROUP BY job_stat;

and the output is given as follows:

{"time":"2020-10-20T16:47:39.117075"} {"db_schema":"USER1"} {"SUBMIT":123,"RUN":456}

I actually want it like this:

{"time":"2020-10-20T16:47:39.117075","db_schema":"USER1", ["SUBMIT":123, "RUN":456]}

So that it's all in one object. Can you help change the query so that this can be achieved?

Please say which version of Oracle you're using. Later versions of Oracle have more capabilities, and that's certainly true with JSON, where Oracle has been adding features to every release since 12c. — APC
– APC, Commented Oct 20, 2020 at 12:17
["SUBMIT":123, "RUN":456] is invalid JSON. Did you mean to not have the [] brackets? Or did you mean something else? — MT0
– MT0, Commented Oct 20, 2020 at 12:22

MT0 · Accepted Answer · 2020-10-20 12:27:01Z

3

If your Oracle version supports it, you can use JSON_MERGEPATCH:

SELECT JSON_MERGEPATCH(
         json_object(
           'time' VALUE localtimestamp,
           'db_schema' VALUE 'USER1'
         ),
         json_objectagg(
           JOB_STAT VALUE COUNT(id)
         )
         RETURNING CLOB
       ) AS json
FROM   table_name
GROUP BY job_stat;

Which, for the sample data:

CREATE TABLE table_name ( id, job_stat ) AS
SELECT LEVEL, 'SUBMIT' FROM DUAL CONNECT BY LEVEL <= 456
UNION ALL
SELECT LEVEL, 'RUN' FROM DUAL CONNECT BY LEVEL <= 123;

Outputs:

| JSON                                                                             |
| :------------------------------------------------------------------------------- |
| {"time":"2020-10-20T13:15:27.947079","db_schema":"USER1","SUBMIT":456,"RUN":123} |

Or, if you want an array, you can aggregate twice:

SELECT json_object(
         'time' VALUE localtimestamp,
         'db_schema' VALUE 'USER1',
         'job_stats' VALUE JSON_ARRAYAGG( json_job_stat )
       ) AS json
FROM   (
  SELECT JSON_OBJECT( JOB_STAT VALUE COUNT(id) ) AS json_job_stat
  FROM   table_name
  GROUP BY job_stat
)

Which outputs:

| JSON                                                                                               |
| :------------------------------------------------------------------------------------------------- |
| {"time":"2020-10-20T13:25:37.406742","db_schema":"USER1","job_stats":[{"SUBMIT":456},{"RUN":123}]} |

db<>fiddle here

edited Oct 20, 2020 at 12:27

answered Oct 20, 2020 at 12:15

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scouse_Bob Over a year ago

Fantastic!! Absolutely love it. Thank you so much you rock.

gsalem · Accepted Answer · 2020-10-20 12:17:30Z

Try something like this:

with fq as (select owner,table_name,count(0) cnt
from all_tab_columns group by owner,table_name)
select json_object('owner' value owner, 'Tables' value json_arrayagg(json_object(table_name value cnt)returning clob ) returning clob) json_val
from fq
group by owner

json_arrayagg returns an array in the json format. It takes an expr as the value, that's why I used json_object inside. This can probably be improved upon to get closer to what you want. The output looks like this (in sqlcl):

{"owner":"XDB","Tables":[{"XDB$H_LINK":8},{"JSON$COLLECTION_METADATA_V":35},{"XD
B$ELEMENT":1},{"JSON$USER_CREDENTIALS":3},{"XDB$DXPTAB":11},{"XDB$DBFS_VIRTUAL_F
OLDER":2},{"X$PT46MP5MDR0M04NE0KWN0SK0K1LN":2},{"PATH_VIEW":4},{"DOCUMENT_LINKS"
:6},{"XDB$STATS":1},{"XDB$RESCONFIG":1},{"XDB$XTAB":11},{"APP_USERS_AND_ROLES":3
},{"X$NM46MP5MDR0M04NE0KWN0SK0K1LN":2},{"XDB$TTSET":4},{"XDB$SIMPLE_TYPE":1},{"X
DB$CHOICE_MODEL":1},{"XDB$GROUP_REF":1},{"XDB$NONCEKEY":1},{"XDB$CHECKOUTS":4},{
"APP_ROLE_MEMBERSHIP":2},{"XDB$NLOCKS":3},{"XDB$TSETMAP":3},{"XDB$IMPORT_TT_INFO
":5},{"XDB$ANYATTR":1},{"JSON$USER_ROLES":3},{"JSON$USERS":5},{"XDB$PATH_INDEX_P
ARAMS":4},{"XDB$ROOT_INFO":18},{"XDB$ATTRGROUP_REF":1},{"XDB$ALL_MODEL":1},{"XDB
$XIDX_IMP_T":5},{"JSON$COLLECTION_METADATA":8},{"XDB$REPOS":16},{"XDB$XTABNMSP":
6},{"XDB$IMPORT_PT_INFO":2},{"XDB$IMPORT_NM_INFO":2},{"XDB$COLUMN_INFO":4},{"XDB
$CDBPORTS":3},{"XDB$SCHEMA":1},{"XDB$ATTRIBUTE":1},{"XDB$RESOURCE":1},{"XDB$ACL"
:1},{"XDB$GROUP_DEF":1},{"XDB$MOUNTS":5},{"XDB$XIDX_PARAM_T":3},{"X$QN46MP5MDR0M
04NE0KWN0SK0K1LN":4},{"RESOURCE_VIEW":3},{"XDB$RCLIST_V":1},{"XDB$ATTRGROUP_DEF"
:1},{"XDB$CONFIG":1},{"XDB_INDEX_DDL_CACHE":11},{"XDB$IMPORT_QN_INFO":4},{"XDB$H
_INDEX":5},{"XDB$XDB_READY":1},{"JSON$USER_COLLECTION_METADATA":7},{"XDB$ROOT_IN
FO_V":18},{"XDB$COMPLEX_TYPE":1},{"XDB$ANY":1},{"XDB$SEQUENCE_MODEL":1},{"XDB$XT
ABCOLS":7},{"XDB$XIDX_PART_TAB":4},{"XDB$D_LINK":4}]}

Collectives™ on Stack Overflow

Bring two json objects into one using oracle sqlplus

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related