Блог

Главная - Вопросы по программированию - Как получить значение внутри JSON, которое находится внутри столбца в таблице в Oracle sql?

Как получить значение внутри JSON, которое находится внутри столбца в таблице в Oracle sql?

#sql #oracle

#sql #Oracle

Вопрос:

Предположим, что у меня есть таблица с именем agents_timesheet , которая имеет такую структуру:

  ID | name |                   health_check_record                  | date        | clock_in | clock_out  
---------------------------------------------------------------------------------------------------------
 1  | AAA  | {"mental":{"stress":"no", "depression":"no"},          | 6-Dec-2021  | 08:25:07 |
    |      |  "physical":{"other_symptoms":"headache", "flu":"no"}} |             |          |
---------------------------------------------------------------------------------------------------------
 2  | BBB  | {"mental":{"stress":"no", "depression":"no"},          | 6-Dec-2021  | 08:26:12 |
    |      |  "physical":{"other_symptoms":"no", "flu":"yes"}}      |             |          |
---------------------------------------------------------------------------------------------------------
 3  | CCC  | {"mental":{"stress":"no", "depression":"severe"},      | 6-Dec-2021  | 08:27:12 |
    |      |  "physical":{"other_symptoms":"cancer", "flu":"yes"}}  |             |          |

Теперь мне нужно, чтобы у всех агентов был грипп в день. Что касается получения flu из одного JSON в Oracle SQL, я уже могу получить его с помощью этого оператора SQL:

 SELECT * FROM JSON_TABLE(
   '{"mental":{"stress":"no", "depression":"no"}, "physical":{"fever":"no", "flu":"yes"}}', '

Что касается получения значений из столбца health_check_record  , я могу получить его, используя  SELECT  инструкцию.

Но как получить значения  flu  в JSON в  health_check_record  этой таблице?

Дополнительный вопрос

На основе таблицы, как я могу получить полный список other_symptoms  , тогда он даст мне такой результат:


  ID | name |  other_symptoms
-------------------------------
 1  | AAA  | headache
 2  | BBB  | no
 3  | CCC  | cancer

Ответ №1:

Вы можете использовать функцию JSON_EXISTS() .

 SELECT *
  FROM agents_timesheet 
 WHERE JSON_EXISTS(health_check_record, '$.physical.flu == "yes"');

Существует также "простой старый способ" без синтаксического анализа JSON только для третьего столбца, как стандартный VARCHAR. Этот способ не будет работать в 100% случаев, но если у вас есть данные таким же образом, как вы описали, этого может быть достаточно.

 SELECT * 
  FROM agents_timesheet 
 WHERE health_check_record LIKE '%"flu":"yes"%';

Комментарии:

1. "Простой старый способ" завершится неудачей, если вокруг : или есть пробелы, может дать неожиданные результаты, если "flu" ключ может появиться в нескольких объектах в JSON, и вам нужен определенный путь.

2. @MT0 конечно, "простой старый способ" не является на 100% пуленепробиваемым. Это зависит от того, как формируются данные, а также от других значений. Я рассматривал данные такие же, как в вопросе, и для этого случая это сработает.

3. Важно отметить, что этот синтаксис JSON доступен в 12c и выше...

4. Не могли бы вы дать мне подробное объяснение о том, что jscol такое, как это работает и зачем это использовать?

5. @DhanaD. извините, я имел в виду health_check_record .

Ответ №2:

Как получить значения flu в JSON в health_check_record этой таблицы?

Из Oracle 12, чтобы получить значения, которые вы можете использовать JSON_TABLE с привязкой CROSS JOIN к таблице:

 SELECT a.id,
       a.name,
       j.*,
       a."DATE",
       a.clock_in,
       a.clock_out
FROM   agents_timesheet a
       CROSS JOIN JSON_TABLE(
         a.health_check_record,
         '

db<>скрипка здесь

Ответ №3:

Вы можете использовать "точечную нотацию" для доступа к данным из столбца JSON. Вот так:

 select "DATE", id, name
from   agents_timesheet t
where  t.health_check_record.physical.flu = 'yes'
;


DATE          ID  NAME
-----------  ---  ----
06-DEC-2021    2  BBB

Обратите внимание, что этот подход требует, чтобы вы использовали псевдоним для имени таблицы (чтобы вы могли использовать его при доступе к данным JSON).

Для тестирования я использовал данные, размещенные MT0 в dbfiddle. Я не большой поклонник имен столбцов в двойных кавычках; используйте что-нибудь другое для "DATE" , например, dt или date_ .

COLUMNS (fever VARCHAR(2) PATH '$.physical.flu')
);
Что касается получения значений из столбца health_check_record , я могу получить его, используя SELECT инструкцию.

Но как получить значения flu в JSON в health_check_record этой таблице?

Дополнительный вопрос

На основе таблицы, как я могу получить полный список other_symptoms , тогда он даст мне такой результат:



Ответ №1:




Вы можете использовать функцию JSON_EXISTS() .




Существует также "простой старый способ" без синтаксического анализа JSON только для третьего столбца, как стандартный VARCHAR. Этот способ не будет работать в 100% случаев, но если у вас есть данные таким же образом, как вы описали, этого может быть достаточно.






Комментарии:




1.  "Простой старый способ" завершится неудачей, если вокруг    :    или есть пробелы, может дать неожиданные результаты, если    "flu"    ключ может появиться в нескольких объектах в JSON, и вам нужен определенный путь.  


2.  @MT0 конечно, "простой старый способ" не является на 100% пуленепробиваемым.   Это зависит от того, как формируются данные, а также от других значений.   Я рассматривал данные такие же, как в вопросе, и для этого случая это сработает.  


3.  Важно отметить, что этот синтаксис JSON доступен в 12c и выше...  


4.  Не могли бы вы дать мне подробное объяснение о том, что    jscol    такое, как это работает и зачем это использовать?  


5.  @DhanaD.   извините, я имел в виду    health_check_record    .  



Ответ №2:






Как получить значения flu в JSON в health_check_record этой таблицы?



Из Oracle 12, чтобы получить значения, которые вы можете использовать  JSON_TABLE  с привязкой  CROSS JOIN  к таблице:




db<>скрипка здесь




Ответ №3:




Вы можете использовать "точечную нотацию" для доступа к данным из столбца JSON. Вот так:




Обратите внимание, что этот подход требует, чтобы вы использовали псевдоним для имени таблицы (чтобы вы могли использовать его при доступе к данным JSON).


Для тестирования я использовал данные, размещенные MT0 в dbfiddle. Я не большой поклонник имен столбцов в двойных кавычках; используйте что-нибудь другое для  "DATE"  , например,  dt  или  date_  .




         COLUMNS (

           mental_stress     VARCHAR2(3) PATH '$.mental.stress',

           mental_depression VARCHAR2(3) PATH '$.mental.depression',

           physical_fever    VARCHAR2(3) PATH '$.physical.fever',

           physical_flu      VARCHAR2(3) PATH '$.physical.flu'

         )

       ) j

WHERE  physical_flu = 'yes';

  


db<>скрипка здесь


Ответ №3:




Вы можете использовать «точечную нотацию» для доступа к данным из столбца JSON. Вот так:




Обратите внимание, что этот подход требует, чтобы вы использовали псевдоним для имени таблицы (чтобы вы могли использовать его при доступе к данным JSON).


Для тестирования я использовал данные, размещенные MT0 в dbfiddle. Я не большой поклонник имен столбцов в двойных кавычках; используйте что-нибудь другое для  "DATE"  , например,  dt  или  date_  .




COLUMNS (fever VARCHAR(2) PATH ‘$.physical.flu’)

);Что касается получения значений из столбца health_check_record  , я могу получить его, используя  SELECT  инструкцию.


Но как получить значения  flu  в JSON в  health_check_record  этой таблице?


Дополнительный вопрос


На основе таблицы, как я могу получить полный список other_symptoms  , тогда он даст мне такой результат:




Ответ №1:




Вы можете использовать функцию JSON_EXISTS() .




Существует также «простой старый способ» без синтаксического анализа JSON только для третьего столбца, как стандартный VARCHAR. Этот способ не будет работать в 100% случаев, но если у вас есть данные таким же образом, как вы описали, этого может быть достаточно.






Комментарии:




1.  «Простой старый способ» завершится неудачей, если вокруг    :    или есть пробелы, может дать неожиданные результаты, если    "flu"    ключ может появиться в нескольких объектах в JSON, и вам нужен определенный путь.  


2.  @MT0 конечно, «простой старый способ» не является на 100% пуленепробиваемым.   Это зависит от того, как формируются данные, а также от других значений.   Я рассматривал данные такие же, как в вопросе, и для этого случая это сработает.  


3.  Важно отметить, что этот синтаксис JSON доступен в 12c и выше…  


4.  Не могли бы вы дать мне подробное объяснение о том, что    jscol    такое, как это работает и зачем это использовать?  


5.  @DhanaD.   извините, я имел в виду    health_check_record    .  



Ответ №2:






Как получить значения flu в JSON в health_check_record этой таблицы?



Из Oracle 12, чтобы получить значения, которые вы можете использовать  JSON_TABLE  с привязкой  CROSS JOIN  к таблице:




db<>скрипка здесь




Ответ №3:




Вы можете использовать «точечную нотацию» для доступа к данным из столбца JSON. Вот так:




Обратите внимание, что этот подход требует, чтобы вы использовали псевдоним для имени таблицы (чтобы вы могли использовать его при доступе к данным JSON).


Для тестирования я использовал данные, размещенные MT0 в dbfiddle. Я не большой поклонник имен столбцов в двойных кавычках; используйте что-нибудь другое для  "DATE"  , например,  dt  или  date_  .










	Метки: ,