Oracle JSON 查询中的动态列列表

您可以创建帮助器函数:

CREATE FUNCTION object_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(js.get_string(keys(i)));
  END LOOP;
  RETURN arr.to_string();
END;
/

然后，您可以使用:

SELECT json_object(
         KEY 'keys' VALUE MIN(p.keys) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(object_to_array(t.json) FORMAT JSON)
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1) t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.json) AS data FROM DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(SUBSTR(path, 3)) AS keys
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(20) PATH '$."o:path"'
                  )
                )
         WHERE  path != '$'
       ) p

其中，对于样本数据:

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');
INSERT INTO tbl1 VALUES ('2','d',NULL);

输出:

Oracle 21 fiddle个

或者，您可以创建两个助手函数:

CREATE FUNCTION object_keys_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(keys(i));
  END LOOP;
  RETURN arr.to_string();
END;
/

CREATE FUNCTION object_values_to_array(
  value IN VARCHAR2
) RETURN VARCHAR2
IS
  js   JSON_OBJECT_T := JSON_OBJECT_T( value );
  arr  JSON_ARRAY_T := JSON_ARRAY_T();
  keys JSON_KEY_LIST := js.get_keys();
BEGIN
  FOR i in 1 .. keys.COUNT LOOP
    arr.append(js.get_string(keys(i)));
  END LOOP;
  RETURN arr.to_string();
END;
/

然后，您可以使用:

SELECT json_object(
         KEY 'keys' VALUE MIN(object_keys_to_array(json)) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(object_values_to_array(json) FORMAT JSON)
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1)

它的输出是相同的.

Oracle 21 fiddle个

@Astentx comments 建议使用不带助手函数的方法:

SELECT JSON_OBJECT(
         KEY 'keys' VALUE MIN(p.keys) FORMAT JSON,
         KEY 'values' VALUE JSON_ARRAYAGG(
           JSON_QUERY(
             t.json FORMAT JSON,
             '$.*'
             WITH UNCONDITIONAL ARRAY WRAPPER
           ) FORMAT JSON
         )
       ) as js
FROM   (SELECT JSON_OBJECT(*) AS json FROM tbl1) t
       CROSS JOIN LATERAL(
         SELECT JSON_DATAGUIDE(t.json) AS data FROM DUAL
       ) d
       CROSS JOIN LATERAL(
         SELECT JSON_ARRAYAGG(SUBSTR(path, 3)) AS keys
         FROM   JSON_TABLE(
                  d.data,
                  '$[*]'
                  COLUMNS(
                    path VARCHAR2(20) PATH '$."o:path"'
                  )
                )
         WHERE  path != '$'
       ) p

它的输出也是一样的.

Oracle 21 fiddle个