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

CodePudding user response：

You can easily use JSON_TABLE() function for this case rather provided the DB version is at least 12.1.0.2 such as

SELECT name
  FROM jsontable,
       JSON_TABLE(jsoncol,
                  '$' COLUMNS(NESTED PATH '$."Company"[*]'
                          COLUMNS(name VARCHAR2 PATH '$."Name"')))

Demo

CodePudding user response：

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