Как я могу использовать оракул REGEXP_SUBSTR для извлечения определенных значений json?

У меня есть несколько столбцов в моей базе данных Oracle, которые содержат json, и для извлечения данных в запросе я использую REGEXP_SUBSTR.

В следующем примере value — это столбец в таблице DOSSIER, содержащий json. Регулярное выражение извлекает значение свойства client.reference в этом json

SELECT REGEXP_SUBSTR(value, '"client"(.*?)"reference":"([^"]+)"', 1, 1, NULL, 2) FROM DOSSIER;

Итак, если json выглядит так:

[...],
"client": {
  "someproperty":"123",
  "someobject": {
    [...]
  },
  "reference":"ABCD",
  "someotherproperty":"456"
},
[...]

SQL-запрос вернет ABDC.

Моя проблема в том, что у некоторых json есть несколько экземпляров клиента, например:

[...],
"contract": {
  "client":"Name of the client",
  "supplier": {
    "reference":"EFGH"
  }
},
[...],
"client": {
  "someproperty":"123",
  "someobject": {
    [...]
  },
  "reference":"ABCD",
  "someotherproperty":"456"
},
[...]

Вы поняли проблему, теперь SQL-запрос вернет EFGH, что является ссылкой поставщика.

Как я могу убедиться, что ссылка содержится в клиенте объекта json?

РЕДАКТИРОВАТЬ: я на Oracle 11g, поэтому я не могу использовать JSON API, и я хотел бы избежать использования стороннего пакета


json regex oracle oracle11g regexp-substr
person Natty    schedule 05.07.2021    source источник
comment
Вы должны использовать Oracle JSON API, а не чистое регулярное выражение.   -  person Tim Biegeleisen    schedule 05.07.2021
comment
@TimBiegeleisen да, я забыл добавить это к вопросу: я работаю на Oracle 11g, поэтому, к сожалению, API json недоступен.   -  person Natty    schedule 06.07.2021

Ответы (1)


arrow_upward
1
arrow_downward

Предполагая, что вы используете Oracle 12c или более позднюю версию, вам следует НЕ использовать регулярные выражения и следует использовать функции Oracle JSON.

Если у вас есть таблица и данные:

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

INSERT INTO table_name (
  value
) VALUES (
  '{
  "contract": {
    "client":"Name of the client",
      "supplier": {
        "reference":"EFGH"
    }
  },
  "client": {
    "someproperty":"123",
    "someobject": {},
    "reference":"ABCD",
    "someotherproperty":"456"
  }
}'
);

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

SELECT JSON_VALUE( value, '$.client.reference' ) AS reference
FROM   table_name;

Что выводит:

REFERENCE
ABCD

db‹›fiddle здесь

Если вы используете Oracle 11 или более раннюю версию, вы можете использовать сторонний пакет PLJSON для анализа JSON в PL/SQL. Например, этот вопрос.

Или включите Java в базе данных, а затем используйте CREATE JAVA (или утилиту loadjava), чтобы добавить класс Java, который может анализировать JSON в базе данных, а затем обернуть его в функцию Oracle и использовать его.

person MT0    schedule 05.07.2021
comment
Спасибо за этот ответ, к сожалению, я использую Oracle 11g, поэтому первый вариант невозможен. Второй вариант может быть аккуратным, но мой клиент не будет устанавливать сторонний пакет на свой сервер, поэтому я тоже застрял на этом конце. Я мог бы использовать их сценарии, чтобы найти способ делать то, что я хочу. - person Natty; 06.07.2021
comment
@Natty Может быть, вы могли бы включить Java в базе данных и использовать класс Java для анализа? Или вы можете предложить клиенту обновить базу данных? (Попытка сделать это с помощью регулярных выражений или других строковых функций не сработает). - person MT0; 06.07.2021
comment
Обновление базы данных — это незавершенная работа над их инфраструктурой (честно говоря, эта работа продолжается уже более года...), поэтому они не хотят использовать третью сторону всего несколько месяцев. Никогда не слышал о Java-коде, используемом в функции Oracle, я обязательно проверю, как это работает! - person Natty; 07.07.2021
comment
@Natty Я ранее дал несколько ответов с примерами; вы можете выполнить поиск в StackOverflow для user:1509264 [oracle] CREATE JAVA, что даст ряд ответов от сложного примера, который распаковывает архивы, до простого привет, мир. - person MT0; 07.07.2021