Retrieve JSON from sql

My json format in one of the sql columns "jsoncol" in the table "jsontable" is like below. Kindly help me to get this data using JSON_QUERY or JSON_VALUE

Please pay attention to the brackets and double quotes in the key value pairs...

{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}

I am trying to retrieve all the company names using sql query. Thanks in advance

by Itsme in StackOverflow on July 02, 2022.

Answer

You can use:

SELECT j.name
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.value,
         '$.Company[*]'
         COLUMNS(
           name VARCHAR2(200) PATH '$.Name'
         )
       ) j

Which, for the sample data:

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

INSERT INTO table_name (value)
VALUES ('{
  "Company": [
    {
      "Info": {
        "Address": "123"
      },
      "Name": "ABC",
      "Id": 999
    },
    {
      "Info": {
        "Address": "456"
      },
      "Name": "XYZ",
      "Id": 888
    }
  ]
}');

Outputs:

NAME
ABC
XYZ

db<>fiddle here

by MT0 on July 02, 2022.