How to generate, parse and use JSON data

In this article, I will explain how easily you can generate, parse and use JSON data in the Oracle database. I used Oracle 19c for all examples in this writing. JSON enhancements have been made since Oracle Database release 12.2. Therefore, numerous of new functionalities have come after the first version of 12c has been released. The following data set has been used in the examples. You can also reach the metadata of the EMP table by the following link.

https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0)
);
insert into emp values(7839, 'KING', 'PRESIDENT', null,  to_date('17/11/1981','dd/mm/yyyy'),  5000, null, 10);

insert into emp values(7698, 'BLAKE', 'MANAGER', 7839, to_date('1/5/1981','dd/mm/yyyy'),  2850, null, 30);

insert into emp values(7782, 'CLARK', 'MANAGER', 7839, to_date('9/6/1981','dd/mm/yyyy'),  2450, null, 10);

insert into emp values(7566, 'JONES', 'MANAGER', 7839, to_date('2/4/1981','dd/mm/yyyy'), 2975, null, 20);

insert into emp values(7788, 'SCOTT', 'ANALYST', 7566, to_date('12/5/87','dd/mm/rr'), 3000, null, 20);

insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3/12/1981','dd/mm/yyyy'), 3000, null, 20);

insert into emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17/12/1980','dd/mm/yyyy'), 800, null, 20);

insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20/2/1981','dd/mm/yyyy'), 1600, 300, 30);

insert into emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22/2/1981','dd/mm/yyyy'), 1250, 500, 30);

insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28/9/1981','dd/mm/yyyy'), 1250, 1400, 30);

insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8/9/1981','dd/mm/yyyy'), 1500, 0, 30);

insert into emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('10/06/87', 'dd/mm/rr'), 1100, null, 20);

insert into emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3/12/1981','dd/mm/yyyy'), 950, null, 30);

insert into emp values(7934, 'MILLER', 'CLERK', 7782, to_date('23/1/1982','dd/mm/yyyy'), 1300, null, 10);

commit;

Our data look like as follows.

SQL> SET LINESIZE 200
SQL> COLUMN EMPNO FORMAT A10
SQL> COLUMN ENAME FORMAT A20
SQL> COLUMN JOB FORMAT A20
SQL> COLUMN MGR FORMAT A5
SQL> COLUMN HIREDATE FORMAT A15
SQL> COLUMN SAL FORMAT A10
SQL> COLUMN COMM FORMAT A10
SQL> COLUMN DEPTNO FORMAT A10
SQL> COLUMN EMPNO FORMAT 99999999
SQL> COLUMN MGR FORMAT 99999999
SQL> COLUMN SAL FORMAT 99999999
SQL> COLUMN COMM FORMAT 99999999
SQL> COLUMN DEPTNO FORMAT 99999999
SQL>
SQL> SELECT * FROM EMP;

    EMPNO ENAME                JOB                        MGR HIREDATE              SAL      COMM    DEPTNO
--------- -------------------- -------------------- --------- --------------- --------- --------- ---------
     7839 KING                 PRESIDENT                      17/11/1981           5000                  10
     7698 BLAKE                MANAGER                   7839 01/05/1981           2850                  30
     7782 CLARK                MANAGER                   7839 09/06/1981           2450                  10
     7566 JONES                MANAGER                   7839 02/04/1981           2975                  20
     7788 SCOTT                ANALYST                   7566 12/05/1987           3000                  20
     7902 FORD                 ANALYST                   7566 03/12/1981           3000                  20
     7369 SMITH                CLERK                     7902 17/12/1980            800                  20
     7499 ALLEN                SALESMAN                  7698 20/02/1981           1600       300        30
     7521 WARD                 SALESMAN                  7698 22/02/1981           1250       500        30
     7654 MARTIN               SALESMAN                  7698 28/09/1981           1250      1400        30
     7844 TURNER               SALESMAN                  7698 08/09/1981           1500         0        30

    EMPNO ENAME                JOB                        MGR HIREDATE              SAL      COMM    DEPTNO
--------- -------------------- -------------------- --------- --------------- --------- --------- ---------
     7876 ADAMS                CLERK                     7788 10/06/1987           1100                  20
     7900 JAMES                CLERK                     7698 03/12/1981            950                  30
     7934 MILLER               CLERK                     7782 23/01/1982           1300                  10

14 sat²rlar² seildi.

Generating JSON Data

There are many functions that you can generate json data. I mentioned the most usable ones. You can combine them in order to create complex json objects. Basically, the intended usage of each one can be understandable from the examples below.

JSON_OBJECT

SELECT 
    JSON_OBJECT(
        KEY 'name' VALUE e.ename,
        KEY 'job' VALUE e.job,
        KEY 'hiredate' VALUE e.hiredate
    ) AS json_data
FROM emp e
WHERE e.deptno = 30;
JSON_OBJECT

JSON_OBJECTAGG

Note that numeric data cannot be used as key in JSON functions.

SELECT 
    JSON_OBJECTAGG(
        KEY to_char(e.empno) VALUE e.ename
    ) AS json_data
FROM emp e
WHERE e.deptno = 30
ORDER BY e.empno;
JSON_OBJECTAGG

JSON_ARRAY

SELECT 
    JSON_ARRAY(
        e.empno,
        JSON_OBJECT(
            KEY 'name' VALUE e.ename,
            KEY 'job' VALUE e.job,
            KEY 'hiredate' VALUE e.hiredate         
        )
    ) AS json_data
FROM emp e
ORDER BY e.empno;
JSON_ARRAY

JSON_ARRAYAGG

Unlike JSON_ARRAY, JSON_ARRAYAGG function combines the result set into a single JSON array. The query result in SQL*Plus is formatted to look properly.

SQL> SELECT
  2      JSON_ARRAYAGG(
  3          JSON_OBJECT(
  4              KEY 'name' VALUE e.ename,
  5              KEY 'job' VALUE e.job,
  6              KEY 'hiredate' VALUE e.hiredate
  7          )
  8          ORDER BY e.empno
  9      ) AS json_data
 10  FROM emp e
 11  order by e.empno;

JSON_DATA
--------------------------------------------------------------------------------
[{"name":"SMITH","job":"CLERK","hiredate":"1980-12-17T00:00:00"},
{"name":"ALLEN","job":"SALESMAN","hiredate":"1981-02-20T00:00:00"},
{"name":"WARD","job":"SALESMAN","hiredate":"1981-02-22T00:00:00"},
{"name":"JONES","job":"MANAGER","hiredate":"1981-04-02T00:00:00"},
{"name":"MARTIN","job":"SALESMAN","hiredate":"1981-09-28T00:00:00"},
{"name":"BLAKE","job":"MANAGER","hiredate":"1981-05-01T00:00:00"},
{"name":"CLARK","job":"MANAGER","hiredate":"1981-06-09T00:00:00"},
{"name":"SCOTT","job":"ANALYST","hiredate":"1987-05-12T00:00:00"},
{"name":"KING","job":"PRESIDENT","hiredate":"1981-11-17T00:00:00"},
{"name":"TURNER","job":"SALESMAN","hiredate":"1981-09-08T00:00:00"},
{"name":"ADAMS","job":"CLERK","hiredate":"1987-06-10T00:00:00"},
{"name":"JAMES","job":"CLERK","hiredate":"1981-12-03T00:00:00"},
{"name":"FORD","job":"ANALYST","hiredate":"1981-12-03T00:00:00"},
{"name":"MILLER","job":"CLERK","hiredate":"1982-01-23T00:00:00"}]



SQL> SELECT
  2      JSON_ARRAYAGG(e.ename) AS json_data
  3  FROM emp e
  4  order by e.empno;

JSON_DATA
--------------------------------------------------------------------------------
["KING","BLAKE","CLARK","JONES","SCOTT","FORD","SMITH","ALLEN","WARD","MARTIN","
TURNER","ADAMS","JAMES","MILLER"]

Converting row to JSON

You can use the wildcard “*” as input to the JSON_OBJECT function in order to convert table rows to JSON without key:value mapping.

SELECT JSON_OBJECT(*) FROM emp;
JSON_OBJECT
SELECT 
    JSON_ARRAYAGG (  
        JSON_OBJECT(*) RETURNING CLOB   
    ) AS JSON_DOC  
FROM emp;
[
   {
      "EMPNO":7839,
      "ENAME":"KING",
      "JOB":"PRESIDENT",
      "MGR":null,
      "HIREDATE":"1981-11-17T00:00:00",
      "SAL":5000,
      "COMM":null,
      "DEPTNO":10
   },
   {
      "EMPNO":7698,
      "ENAME":"BLAKE",
      "JOB":"MANAGER",
      "MGR":7839,
      "HIREDATE":"1981-05-01T00:00:00",
      "SAL":2850,
      "COMM":null,
      "DEPTNO":30
   },
   {
      "EMPNO":7782,
      "ENAME":"CLARK",
      "JOB":"MANAGER",
      "MGR":7839,
      "HIREDATE":"1981-06-09T00:00:00",
      "SAL":2450,
      "COMM":null,
      "DEPTNO":10
   },
   {
      "EMPNO":7566,
      "ENAME":"JONES",
      "JOB":"MANAGER",
      "MGR":7839,
      "HIREDATE":"1981-04-02T00:00:00",
      "SAL":2975,
      "COMM":null,
      "DEPTNO":20
   },
   {
      "EMPNO":7788,
      "ENAME":"SCOTT",
      "JOB":"ANALYST",
      "MGR":7566,
      "HIREDATE":"1987-05-12T00:00:00",
      "SAL":3000,
      "COMM":null,
      "DEPTNO":20
   },
   {
      "EMPNO":7902,
      "ENAME":"FORD",
      "JOB":"ANALYST",
      "MGR":7566,
      "HIREDATE":"1981-12-03T00:00:00",
      "SAL":3000,
      "COMM":null,
      "DEPTNO":20
   },
   {
      "EMPNO":7369,
      "ENAME":"SMITH",
      "JOB":"CLERK",
      "MGR":7902,
      "HIREDATE":"1980-12-17T00:00:00",
      "SAL":800,
      "COMM":null,
      "DEPTNO":20
   },
   {
      "EMPNO":7499,
      "ENAME":"ALLEN",
      "JOB":"SALESMAN",
      "MGR":7698,
      "HIREDATE":"1981-02-20T00:00:00",
      "SAL":1600,
      "COMM":300,
      "DEPTNO":30
   },
   {
      "EMPNO":7521,
      "ENAME":"WARD",
      "JOB":"SALESMAN",
      "MGR":7698,
      "HIREDATE":"1981-02-22T00:00:00",
      "SAL":1250,
      "COMM":500,
      "DEPTNO":30
   },
   {
      "EMPNO":7654,
      "ENAME":"MARTIN",
      "JOB":"SALESMAN",
      "MGR":7698,
      "HIREDATE":"1981-09-28T00:00:00",
      "SAL":1250,
      "COMM":1400,
      "DEPTNO":30
   },
   {
      "EMPNO":7844,
      "ENAME":"TURNER",
      "JOB":"SALESMAN",
      "MGR":7698,
      "HIREDATE":"1981-09-08T00:00:00",
      "SAL":1500,
      "COMM":0,
      "DEPTNO":30
   },
   {
      "EMPNO":7876,
      "ENAME":"ADAMS",
      "JOB":"CLERK",
      "MGR":7788,
      "HIREDATE":"1987-06-10T00:00:00",
      "SAL":1100,
      "COMM":null,
      "DEPTNO":20
   },
   {
      "EMPNO":7900,
      "ENAME":"JAMES",
      "JOB":"CLERK",
      "MGR":7698,
      "HIREDATE":"1981-12-03T00:00:00",
      "SAL":950,
      "COMM":null,
      "DEPTNO":30
   },
   {
      "EMPNO":7934,
      "ENAME":"MILLER",
      "JOB":"CLERK",
      "MGR":7782,
      "HIREDATE":"1982-01-23T00:00:00",
      "SAL":1300,
      "COMM":null,
      "DEPTNO":10
   }
]

JSON_DATAGUIDE

As the name suggests, JSON_DATAGUIDE function returns the metadata of the given JSON input.

SELECT 
    JSON_SERIALIZE(
            JSON_DATAGUIDE(JSON_DATA) 
            PRETTY)
FROM (
    SELECT 
        JSON_ARRAYAGG(JSON_OBJECT(*)) JSON_DATA
    FROM EMP
);
[
  {
    "o:path" : "$.JOB",
    "type" : "string",
    "o:length" : 16
  },
  {
    "o:path" : "$.MGR",
    "type" : "string",
    "o:length" : 4
  },
  {
    "o:path" : "$.SAL",
    "type" : "number",
    "o:length" : 4
  },
  {
    "o:path" : "$.COMM",
    "type" : "string",
    "o:length" : 4
  },
  {
    "o:path" : "$.EMPNO",
    "type" : "number",
    "o:length" : 4
  },
  {
    "o:path" : "$.ENAME",
    "type" : "string",
    "o:length" : 8
  },
  {
    "o:path" : "$.DEPTNO",
    "type" : "number",
    "o:length" : 2
  },
  {
    "o:path" : "$.HIREDATE",
    "type" : "string",
    "o:length" : 32
  }
]

Format the Output

Mostly, JSON_SERIALIZE function can be used to convert your JSON document which is held any supported data type to text. Besides, you can use this function in order to format your data. The PRETTY keyword displays the output more readable format.

SELECT 
    JSON_SERIALIZE('[{"EMPNO":"7839","ENAME":"KING"},{"EMPNO":"7698","ENAME":"BLAKE"},{"EMPNO":"7782","ENAME":"CLARK"}]' PRETTY) 
FROM DUAL;
[
  {
    "EMPNO" : "7839",
    "ENAME" : "KING"
  },
  {
    "EMPNO" : "7698",
    "ENAME" : "BLAKE"
  },
  {
    "EMPNO" : "7782",
    "ENAME" : "CLARK"
  }
]

Furthermore, you can specify the returning output of the data type.

SELECT 
    JSON_SERIALIZE(CLOB_COLUMN RETURNING CLOB PRETTY) 
FROM T_TAB;

Parsing JSON Data

In order to parse JSON data I will use JSON_TABLE function. For my examples, I will use the following scripts.

CREATE TABLE T_JSON_DOC
(
    ID RAW(16) PRIMARY KEY,
    DATA CLOB,
    CONSTRAINT JSON_DOC_CHK CHECK (DATA IS JSON)
);
INSERT INTO T_JSON_DOC 
VALUES(SYS_GUID(), 
'{
   "brand":"BMW",
   "model":"4.20d",
   "year":"2015",
   "country":"Germany",
   "price":"15000",
   "engine":{
      "number_of_cylinders":"8 cylinder",
      "fuel_system":"4BBL carburetor"
   }
}');

INSERT INTO T_JSON_DOC 
VALUES(SYS_GUID(), 
'{
   "brand":"BMW",
   "model":"5.30d",
   "year":"2016",
   "country":"Germany",
   "price":"25000",
   "engine":{
      "number_of_cylinders":"4 cylinder",
      "fuel_system":"fuel injected"
   }
}');

INSERT INTO T_JSON_DOC 
VALUES(SYS_GUID(), 
'{
   "brand":"Mercedes",
   "model":"slk200",
   "year":"2005",
   "country":"Germany",
   "price":"10000",
   "engine":{
      "number_of_cylinders":"4 cylinder",
      "fuel_system":"4BBL carburetor"
   }
}');

INSERT INTO T_JSON_DOC 
VALUES(SYS_GUID(), 
'{
   "brand":"FORD",
   "model":"Focus",
   "year":"2013",
   "country":"USA",
   "price":"8000",
   "engine":{
      "number_of_cylinders":"8 cylinder",
      "fuel_system":"fuel injected"
   }
}');

INSERT INTO T_JSON_DOC 
VALUES(SYS_GUID(), 
'{
   "brand":"Porche",
   "model":"Carrera",
   "year":"2016",
   "country":"Germany",
   "price":"12000",
   "engine":{
      "number_of_cylinders":"8 cylinder",
      "fuel_system":"4BBL carburetor"
   }
}');

COMMIT;

Example of JSON_TABLE with dot notation.

SELECT 
    t.*
FROM T_JSON_DOC,
     JSON_TABLE(data, '$'
          COLUMNS (brand                VARCHAR2(50 CHAR) PATH '$.brand',
                   model                VARCHAR2(50 CHAR) PATH '$.model',
                   year                 NUMBER            PATH '$.year',
                   country              VARCHAR2(50 CHAR) PATH '$.country',
                   price                VARCHAR2(20 CHAR) PATH '$.price',
                   number_of_cylinders  VARCHAR2(20 CHAR) PATH '$.engine.number_of_cylinders',
                   fuel_system          VARCHAR2(20 CHAR) PATH '$.engine.fuel_system'
                  )
              ) t;
JSON_TABLE

With the version of Oracle 18c, we can use the path expressions instead of dot notation as shown below.

SELECT 
    t.*
FROM T_JSON_DOC,
     JSON_TABLE(data, '$'
          COLUMNS (brand                VARCHAR2(50 CHAR) PATH brand,
                   model                VARCHAR2(50 CHAR) PATH model,
                   year                 NUMBER            PATH year,
                   country              VARCHAR2(50 CHAR) PATH country,
                   price                VARCHAR2(20 CHAR) PATH price,
                   number_of_cylinders  VARCHAR2(20 CHAR) PATH engine.number_of_cylinders,
                   fuel_system          VARCHAR2(20 CHAR) PATH engine.fuel_system
                  )
              ) t;
JSON_TABLE

Moreover, you don’t need any function to parse the JSON data. With using dot notation, you can easily use it as a predicate as shown below.

SQL> SET LINESIZE 200
SQL> COLUMN brand FORMAT A15
SQL> COLUMN model FORMAT A15
SQL> COLUMN year FORMAT A15
SQL> COLUMN country FORMAT A15
SQL> COLUMN price FORMAT A15
SQL>
SQL> SELECT
  2      t.data.brand,
  3      t.data.model,
  4      t.data.year,
  5      t.data.country,
  6      t.data.price
  7  FROM T_JSON_DOC t
  8  where t.data.brand = 'Porche';

BRAND           MODEL           YEAR            COUNTRY         PRICE
--------------- --------------- --------------- --------------- ---------------
Porche          Carrera         2016            Germany         12000


SQL> SELECT
  2      t.data.brand,
  3      t.data.model,
  4      t.data.country,
  5      t.data.price
  6  FROM T_JSON_DOC t
  7  where t.data.price > 10000;

BRAND           MODEL           COUNTRY         PRICE
--------------- --------------- --------------- ---------------
BMW             4.20d           Germany         15000
BMW             5.30d           Germany         25000
Porche          Carrera         Germany         12000

How to convert XML to JSON

WITH
XML_TAB as (
select
    XMLTYPE.createXML('<?xml version="1.0" encoding="UTF-8"?>
    <cars>
        <car>
          <brand>BMW</brand>
          <model>4.20d</model>
          <year>2015</year>
          <country>Germany</country>
          <price>15000</price>
          <engine>
              <number_of_cylinders>8 cylinder</number_of_cylinders>
              <fuel_system>4BBL carburetor</fuel_system>
          </engine>
        </car>
        <car>
          <brand>BMW</brand>
          <model>5.30d</model>
          <year>2016</year>
          <country>Germany</country>
          <price>25000</price>
          <engine>
              <number_of_cylinders>4 cylinder</number_of_cylinders>
              <fuel_system>fuel injected</fuel_system>
          </engine>          
        </car>
        <car>
          <brand>Mercedes</brand>
          <model>slk200</model>
          <year>2005</year>
          <country>Germany</country>
          <price>10000</price>
          <engine>
              <number_of_cylinders>4 cylinder</number_of_cylinders>
              <fuel_system>4BBL carburetor</fuel_system>
          </engine>          
        </car>
        <car>
          <brand>FORD</brand>
          <model>Focus</model>
          <year>2013</year>
          <country>USA</country>
          <price>8000</price>   
          <engine>
              <number_of_cylinders>8 cylinder</number_of_cylinders>
              <fuel_system>fuel injected</fuel_system>
          </engine>          
        </car>
        <car>
          <brand>Porche</brand>
          <model>Carrera</model>
          <year>2016</year>
          <country>Germany</country>
          <price>12000</price>
          <engine>
              <number_of_cylinders>8 cylinder</number_of_cylinders>
              <fuel_system>4BBL carburetor</fuel_system>
          </engine>          
        </car>                
    </cars>') XML_COL from dual
)
SELECT 
    JSON_ARRAYAGG(
        JSON_OBJECT(KEY 'car-'||to_char(rownum) VALUE
            JSON_OBJECT(
                     KEY 'brand' VALUE t."brand",
                     KEY 'model' VALUE t."model",
                     KEY 'year' VALUE t."year",
                     KEY 'country' VALUE t."country",
                     KEY 'price' VALUE t."price",
                     KEY 'engine' VALUE 
                        JSON_OBJECT(
                                    KEY 'number_of_cylinders' VALUE t."number_of_cylinders", 
                                    KEY 'fuel_system' VALUE t."fuel_system"
                                   )
                       ) 
                   )
                )               
FROM (  
        SELECT
            a.*
        FROM XML_TAB, XMLTABLE('/cars/*' passing XML_TAB.XML_COL
                                columns 
                                    "brand" path 'brand'
                                   ,"model" path 'model'
                                   ,"year" path 'year'
                                   ,"country" path 'country'
                                   ,"price" path 'price'
                                   ,"number_of_cylinders" path 'engine/number_of_cylinders'
                                   ,"fuel_system" path 'engine/fuel_system'
        ) a 
) t;
[
   {
      "car-1":{
         "brand":"BMW",
         "model":"4.20d",
         "year":"2015",
         "country":"Germany",
         "price":"15000",
         "engine":{
            "number_of_cylinders":"8 cylinder",
            "fuel_system":"4BBL carburetor"
         }
      }
   },
   {
      "car-2":{
         "brand":"BMW",
         "model":"5.30d",
         "year":"2016",
         "country":"Germany",
         "price":"25000",
         "engine":{
            "number_of_cylinders":"4 cylinder",
            "fuel_system":"fuel injected"
         }
      }
   },
   {
      "car-3":{
         "brand":"Mercedes",
         "model":"slk200",
         "year":"2005",
         "country":"Germany",
         "price":"10000",
         "engine":{
            "number_of_cylinders":"4 cylinder",
            "fuel_system":"4BBL carburetor"
         }
      }
   },
   {
      "car-4":{
         "brand":"FORD",
         "model":"Focus",
         "year":"2013",
         "country":"USA",
         "price":"8000",
         "engine":{
            "number_of_cylinders":"8 cylinder",
            "fuel_system":"fuel injected"
         }
      }
   },
   {
      "car-5":{
         "brand":"Porche",
         "model":"Carrera",
         "year":"2016",
         "country":"Germany",
         "price":"12000",
         "engine":{
            "number_of_cylinders":"8 cylinder",
            "fuel_system":"4BBL carburetor"
         }
      }
   }
]

You can also convert JSON data to XML with using DBMS_XMLGEN.GETXMLTYPE function.

SELECT
    DBMS_XMLGEN.GETXMLTYPE
    (q'[
        SELECT
            t.*
        FROM JSON_TABLE('{
                           "brand":"Porche",
                           "model":"Carrera",
                           "year":"2016",
                           "country":"Germany",
                           "price":"12000",
                           "engine":{
                              "number_of_cylinders":"8 cylinder",
                              "fuel_system":"4BBL carburetor"
                           }
                         }', '$'
                  COLUMNS (brand                VARCHAR2(50 CHAR) PATH '$.brand',
                           model                VARCHAR2(50 CHAR) PATH '$.model',
                           year                 NUMBER            PATH '$.year',
                           country              VARCHAR2(50 CHAR) PATH '$.country',
                           price                VARCHAR2(20 CHAR) PATH '$.price',
                           number_of_cylinders  VARCHAR2(20 CHAR) PATH '$.engine.number_of_cylinders',
                           fuel_system          VARCHAR2(20 CHAR) PATH '$.engine.fuel_system'
                          )
                      ) t
    ]')
FROM DUAL;
<ROWSET>
  <ROW>
    <BRAND>Porche</BRAND>
    <MODEL>Carrera</MODEL>
    <YEAR>2016</YEAR>
    <COUNTRY>Germany</COUNTRY>
    <PRICE>12000</PRICE>
    <NUMBER_OF_CYLINDERS>8 cylinder</NUMBER_OF_CYLINDERS>
    <FUEL_SYSTEM>4BBL carburetor</FUEL_SYSTEM>
  </ROW>
</ROWSET>

I hope this will help you 🙂

Posted in SQL

3 thoughts on “How to generate, parse and use JSON data

  1. Here a question, how to get a JSON from a JSON? I have the follow situation: The JSON response from API request is complex, inside of it we have records, those records are also JSON STRINGS, so I need to parse to read them, but HOW do get only the JSON that is relative of each record as I pick them?

    Example bellow, there is a JSON response with 3 results, how to get each result also as JSON:
    {“header1”: “val_header1”,
    “header2”: “val_header2”,
    “header3”: “val_header3”,
    “header4”: “”,
    “header5”: “val_header5”,
    “header6”: [
    “val_header6”
    ],
    “_embedded”: {
    “results”: [
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    },
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    },
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    }
    ]
    }
    }

  2. Hello Johann, you can use JSON_TABLE function as below.

    select *
    from json_table(

    {“header1”: “val_header1”,
    “header2”: “val_header2”,
    “header3”: “val_header3”,
    “header4”: “”,
    “header5”: “val_header5”,
    “header6”: [
    “val_header6”
    ],
    “_embedded”: {
    “results”: [
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    },
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    },
    { “col1”: “value1”,
    “col2”: “value2”,
    “col3”: “value3”,
    “col4”: “value4”,
    “col5”: “value5”,
    “col6”: [
    {
    “col6a”: “”,
    “col6b”: “value6b”
    }
    ],
    “col7a”: {
    “col7a1”: {
    “col7a1a”: “value7a1a”,
    “col7a1b”: “value7a1b”,
    “col7a1c”: “value7a1c”
    },
    “col7b1”: [
    {
    “col7b1a”: “value7a1a-1”,
    “col7b1b”: “value7a1a-1”,
    “col7b1c”: “value7a1a-1”
    },
    {
    “col7b1a”: “value7a1a-2”,
    “col7b1b”: “value7a1a-2”,
    “col7b1c”: “value7a1a-2”
    }
    ],
    },
    “_embedded”: {}
    }
    ]
    }
    }
    ‘, ‘$’
    columns
    header1 varchar2(50) path ‘$.header1’,
    header6 varchar2(50) path ‘$.header6[*]’,
    nested path ‘$._embedded.results[*]’
    columns (
    col1 varchar2(50) path ‘$.col1’,
    col2 varchar2(50) path ‘$.col2’,
    col3 varchar2(50) path ‘$.col3’,
    col5 varchar2(50) path ‘$.col5’,
    col6 varchar2(50) path ‘$.col6[*].col6b’,
    col7a1a varchar2(50) path ‘$.col7a.col7a1.col7a1a’
    )
    );

Leave a Reply

Your email address will not be published. Required fields are marked *