时间戳

首页 > 解决方案 > 使用 oracle sqlplus 将两个 json 对象合二为一

sql - 使用 oracle sqlplus 将两个 json 对象合二为一

问题描述

我有以下查询：

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;

输出如下：

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

我实际上想要这样：

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

因此，这一切都在一个对象中。您可以帮助更改查询以实现此目的吗？

标签： sqljsonoracle

解决方案

如果您的 Oracle 版本支持它，您可以使用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;

其中，对于样本数据：

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;

输出：

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

或者，如果你想要一个数组，你可以聚合两次：

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
)

哪个输出：

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

db<>在这里摆弄

推荐阅读