Парсинг JSON в Oracle SQL без знания имен входных полей

На чтение 4 мин Опубликовано 16.09.2024

Мне нужно разобрать JSON с помощью SQL-запроса Oracle. Вот мой JSON:

{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}

Я хочу получить результаты в следующем формате:

field_name1:abcd
field_name3:efgh    
field_name4:ijkl

исключая те, которые имеют null в поле value.

Как мне это сделать? Я пробовал использовать JSON_TABLE, но не смог, потому что не знаю имена столбцов заранее.

Вы можете использовать:

WITH FUNCTION get_key(
    pos  IN PLS_INTEGER,
    json IN CLOB
  ) RETURN VARCHAR2 
  AS
    doc_keys JSON_KEY_LIST;
  BEGIN
    doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
    RETURN doc_keys( pos );
  END get_key;
SELECT j.result_no,
       GET_KEY(j.key_no, j.result) AS key,
       j.display_value,
       j.link,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.data,
         '$.result[*]'
         COLUMNS
           result_no     FOR ORDINALITY,
           result        CLOB FORMAT JSON PATH '$',
           NESTED PATH '$.*' COLUMNS (
             key_no        FOR ORDINALITY,
             display_value VARCHAR2(20)  PATH '$.display_value',
             link          VARCHAR2(250) PATH '$.link',
             value         VARCHAR2(20)  PATH '$.value'
           )
       ) j

Что для образца данных:

CREATE TABLE table_name (data CLOB CHECK (data IS JSON) );

INSERT INTO table_name (data)
VALUES (
  '{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}'
)

Выводит:

RESULT_NO	KEY	DISPLAY_VALUE	LINK	VALUE
1	field_name1	TEST1	https://test1.com	abcd
1	field_name2	null	null	null
1	field_name3	TEST3	https://test3.com	efgh
1	field_name4	TEST4	https://test3.com	ijkl

fiddle

Содержание

Ответ
Шаг 1: Создание таблицы с JSON данными
Шаг 2: Создание функции для извлечения ключей
Шаг 3: Запрос для разбора JSON
Шаг 4: Результат
Итог

Ответ

Для разбора JSON в Oracle SQL, когда имена полей заранее неизвестны, вы можете использовать JSON_TABLE с помощью функции, которая извлекает ключи динамически. Приведем пример, используя ваш JSON.

Шаг 1: Создание таблицы с JSON данными

Сначала создайте таблицу и вставьте в нее ваши JSON данные:

CREATE TABLE table_name (data CLOB CHECK (data IS JSON));

INSERT INTO table_name (data)
VALUES (
  '{"result":[
  {
   "field_name1":{"display_value":"TEST1","link":"https://test1.com","value":"abcd"},
   "field_name2":{"display_value":"","link":"","value":""},
   "field_name3":{"display_value":"TEST3","link":"https://test3.com","value":"efgh"},
   "field_name4":{"display_value":"TEST4","link":"https://test3.com","value":"ijkl"}
  }
]}'
);

Шаг 2: Создание функции для извлечения ключей

Создайте функцию, которая будет извлекать ключи из JSON объекта:

WITH FUNCTION get_key(
    pos  IN PLS_INTEGER,
    json IN CLOB
) RETURN VARCHAR2 
AS
    doc_keys JSON_KEY_LIST;
BEGIN
    doc_keys := JSON_OBJECT_T.PARSE(json).GET_KEYS;
    RETURN doc_keys(pos);
END get_key;

Шаг 3: Запрос для разбора JSON

Теперь выполните запрос, который использует JSON_TABLE и вашу функцию для извлечения данных:

SELECT 
    j.result_no, 
    GET_KEY(j.key_no, j.result) AS key, 
    j.value
FROM 
    table_name t
CROSS APPLY 
    JSON_TABLE(
        t.data,
        '$.result[*]'
        COLUMNS
            result_no     FOR ORDINALITY,
            result        CLOB FORMAT JSON PATH '$',
            NESTED PATH '$.*' COLUMNS (
                key_no        FOR ORDINALITY,
                display_value VARCHAR2(20)  PATH '$.display_value',
                link          VARCHAR2(250) PATH '$.link',
                value         VARCHAR2(20)  PATH '$.value'
            )
    ) j
WHERE 
    j.value IS NOT NULL AND j.value <> '';

Шаг 4: Результат

Запрос вернет данные в следующем формате, исключая значения NULL:

field_name1: abcd
field_name3: efgh
field_name4: ijkl

Итог

Таким образом, вы сможете динамически извлекать ключи и значения из JSON, не зная заранее имена полей, и исключать значения NULL.