Блог

Главная - Вопросы по программированию - Запрос Oracle JSON

Запрос Oracle JSON

#sql #json #oracle #oracle12c

Вопрос:

У меня есть столбец JSON в таблице persons, в котором хранятся лучшие мероприятия, которые люди проводили в странах, которые они посетили, как это…

 {
  "countries": [
    {
      "name": "Ireland",
      "bestActivity": {
        "name": "Drinking"
      }
    },
    {
      "name": "Scotland",
      "bestActivity": {
        "name": "Dancing"
      }
    }
  ]
}

Кто-то еще может быть:

 {
  "countries": [
    {
      "name": "Ireland",
      "bestActivity": {
        "name": "Football"
      }
        },
    {
      "name": "England",
      "bestActivity": {
        "name": "Golf"
      }
        }
  ]
}

Я хочу отобрать всех людей, которые посетили Ирландию и с лучшей активностью употребления алкоголя в Ирландии, а также посетили Шотландию и с лучшей активностью употребления алкоголя.

По-настоящему борется. Какие-нибудь советы?

Ответ №1:

Вы можете использовать функцию JSON_EXISTS для фильтрации таблицы на основе описанных вами критериев. Я построил образец запроса ниже, используя структуры JSON, которые вы указали в своем вопросе.

 WITH
    sample_table (first_name, json_col)
    AS
        (SELECT 'John', '{
     "countries": [
        { 
            "name": "Ireland",
            "bestActivity" : {
                 "name": "Drinking"
             }, 
        },
        { 
            "name": "Scotland",
            "bestActivity" : {
                  "name": "Dancing"
             }
        }
 }' FROM DUAL
         UNION ALL
         SELECT 'Jane', '{
     "countries": [
        { 
            "name": "Ireland",
            "bestActivity" : {
                 "name": "Football"
             }, 
        },
        { 
            "name": "England",
            "bestActivity" : {
                  "name": "Golf"
             }
        }
 }' FROM DUAL)
SELECT *
  FROM sample_table s
 WHERE     JSON_EXISTS (s.json_col, '$.countries[*]?(@.name == "Scotland")')
       AND JSON_EXISTS (
               s.json_col,
               '$.countries[*]?(@.name == "Ireland" amp;amp; @.bestActivity.name == "Drinking")');

Ответ №2:

Вы можете использовать JSON_TABLE() функцию в соответствии с версией вашей базы 12c данных (для всех субверсий), например

 SELECT first_name
  FROM t,   
  JSON_TABLE(jscol, '$.countries[*]' 
       COLUMNS ( 
                country      VARCHAR2(90) PATH '$.name',  
                bestActivity VARCHAR2(90) PATH '$.bestActivity.name'
               )
            )
 WHERE (country = 'Ireland' AND bestActivity = 'Drinking')
    OR  country = 'Scotland'
 GROUP BY first_name
 HAVING COUNT(*)>1

Demo

Вы можете использовать JSON_EXISTS() функцию, если версия базы данных находится 12cR2 в таком формате

 SELECT first_name
  FROM t
 WHERE JSON_EXISTS(jscol,
                    '$.countries?(@.name == "Ireland"
                               amp;amp; @.bestActivity.name == "Drinking")')
   AND JSON_EXISTS(jscol,                            
                    '$.countries?(@.name == "Scotland")');

Demo

Обратите внимание, что запрос следует вызывать после выдачи SET DEFINE OFF , когда SQL*Plus используется, чтобы предотвратить amp;amp; интерпретацию операндов как переменной подстановки.

Метки: