Skip to Main Content
  • Questions

  • Converting JSON object into to Rows which have Parent child relation

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ranga Prasad.

Asked: April 10, 2017 - 12:17 pm UTC

Last updated: April 12, 2017 - 12:33 pm UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hello Tom,

We are getting below JSON data from front end application, for forming SQL condition like

"(price < 10.25 OR (category = 2 OR category = 1) OR (name like '%test%' OR in_stock=0))".

How I can convert below JSON data into relational table data i.e Rows with parent child relation to apply hierarchical query's on data.

{
  "condition": "AND",
  "rules": [
    {
      "id": "price",
      "field": "price",
      "type": "double",
      "input": "number",
      "operator": "less",
      "value": "10.25"
    },
    {
      "condition": "OR",
      "rules": [
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "2"
        },
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "1"
        }
      ]
    },
    {
      "condition": "OR",
      "rules": [
        {
          "id": "name",
          "field": "name",
          "type": "string",
          "input": "text",
          "operator": "contains",
          "value": "test"
        },
        {
          "id": "in_stock",
          "field": "in_stock",
          "type": "integer",
          "input": "radio",
          "operator": "equal",
          "value": "0"
        }
      ]
    }
  ],
  "valid": true
}

Thanks in advance
S Ranga Prasad

and Chris said...

You can use json_table to convert a JSON document to relational data. For example:

create table t (
  jdoc varchar2(2000)
);

insert into t values ('{
  "condition": "AND",
  "rules": [
    {
      "id": "price",
      "field": "price",
      "type": "double",
      "input": "number",
      "operator": "less",
      "value": "10.25"
    },
    {
      "condition": "OR",
      "rules": [
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "2"
        },
        {
          "id": "category",
          "field": "category",
          "type": "integer",
          "input": "select",
          "operator": "equal",
          "value": "1"
        }
      ]
    },
    {
      "condition": "OR",
      "rules": [
        {
          "id": "name",
          "field": "name",
          "type": "string",
          "input": "text",
          "operator": "contains",
          "value": "test"
        },
        {
          "id": "in_stock",
          "field": "in_stock",
          "type": "integer",
          "input": "radio",
          "operator": "equal",
          "value": "0"
        }
      ]
    }
  ],
  "valid": true
}');

select j.condition, j.valid, substr(j.rules, 1, 20) 
from   t, json_table(jdoc, '$'
   columns (
     condition varchar2(10) path '$.condition',
     rules     varchar2(1000) format json without wrapper path '$.rules',
     valid varchar2(10) path '$.valid'
   )
) j;

CONDITION  VALID  SUBSTR(J.RULES,1,20)  
AND        true   [{"id":"price","fiel


You have multiple levels of arrays. To extract all of these out, you need to make multiple calls to json_table. Note you need to use the /*+ no_merge */ hint to chain these or you'll get an "ORA-40556: unsupported chaining of JSON_TABLE" error. 

with vals as (
  select /*+ no_merge */j.condition, j.valid, j.rules
from   t, json_table(jdoc, '$'
   columns (
     condition varchar2(10) path '$.condition',
     rules     varchar2(1000) format json path '$.rules',
     valid varchar2(10) path '$.valid'
   )
) j
)
  select j.* from vals, json_table(rules, '$[*]'
    columns (
      id varchar2(20) path '$.id',
      field varchar2(20) path '$.field',
      type varchar2(20) path '$.type'
    )  
  ) j;

ID     FIELD  TYPE    
price  price  double


http://docs.oracle.com/database/121/SQLRF/functions092.htm

If you're lucky enough to be on 12.2, you could check out the JSON data guide. This allows you to expose sections of a JSON document as virtual columns in your table:

http://docs.oracle.com/database/122/ADJSN/json-dataguide.htm

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Why not use NESTED

Stew Ashton, April 11, 2017 - 10:12 pm UTC

Unlike XMLTABLE, you don't have to chain JSON_TABLE invocations to access nested objects or arrays.
select j.*
from t,
json_table(jdoc, '$'
  columns (
    condition varchar2(9) path '$.condition',
    valid varchar2(5) path '$.valid',
    nested path '$.rules[*]'
    columns (
      dad_num for ordinality,
      dad_condition varchar2(8) path '$.condition',
      dad_id varchar2(6) path '$.id',
      dad_field varchar2(10) path '$.field',
      dad_type varchar2(9) path '$.type',
      nested path '$.rules[*]'
      columns (
        son_num for ordinality,
        son_id varchar2(10) path '$.id',
        son_field varchar2(10) path '$.field',
        son_type varchar2(10) path '$.type'
      )  
    )  
  )
) j;

CONDITION VALID    DAD_NUM DAD_COND DAD_ID DAD_FIELD  DAD_TYPE     SON_NUM SON_ID     SON_FIELD  SON_TYPE
--------- ----- ---------- -------- ------ ---------- --------- ---------- ---------- ---------- --------
AND       true           1          price  price      double                                               
AND       true           2 OR                                            1 category   category   integer
AND       true           2 OR                                            2 category   category   integer
AND       true           3 OR                                            1 name       name       string
AND       true           3 OR                                            2 in_stock   in_stock   integer

If there is unlimited recursion...

Stew Ashton, April 12, 2017 - 12:12 pm UTC

If there is no way of knowing how deep the recursion will be, then XML would be easier because Oracle supports the XQUERY language. For JSON, I tried using recursive subquery factoring:
with q1(dad, son, dad_cond, CONDITION, VALID, RULES, ID, FIELD, TYPE, INPUT, OPERATOR, VALUE) as (
  select null, lvl, null, CONDITION, VALID, RULES, ID, FIELD, TYPE, INPUT, OPERATOR, VALUE
  from t,
  json_table(jdoc, '$'
    columns (
      condition varchar2(10) path '$.condition',
      valid     varchar2(10) path '$.valid',
      nested path '$.rules[*]'
      columns (
        lvl for ordinality,
        rules varchar2(4000) format json without wrapper path '$',
        id        varchar2(10) path '$.id',
        field     varchar2(10) path '$.field',
        type      varchar2(10) path '$.type',
        input     varchar2(10) path '$.input',
        operator  varchar2(10) path '$.operator',
        value     varchar2(10) path '$.value'
      )
    )
  ) n
  union all
  select o.son, n.lvl, o.CONDITION, n.CONDITION, o.VALID, n.RULES, n.ID, n.FIELD, n.TYPE, n.INPUT, n.OPERATOR, n.VALUE
  from q1 o, 
  json_table(rules, '$'
    columns (
      condition varchar2(10) path '$.condition',
      nested path '$.rules[*]'
      columns (
        lvl for ordinality,
        rules varchar2(4000) format json without wrapper path '$',
        id        varchar2(10) path '$.id',
        field     varchar2(10) path '$.field',
        type      varchar2(10) path '$.type',
        input     varchar2(10) path '$.input',
        operator  varchar2(10) path '$.operator',
        value     varchar2(10) path '$.value'
      )
    )
  ) n
  where o.rules is not null and o.id is null
) search depth first by valid set order1
select
  start_paren || field ||' '|| operator ||' '|| value ||' '|| end_paren
  as txt
from (
  select order1, dad, son, CONDITION, VALID, ID, FIELD, TYPE, INPUT, OPERATOR, VALUE,
    case when son = 1 and dad is not null then '( ' end start_paren,
    case when order1 = max(order1) over() and dad is null then null
      when order1 = max(order1) over() then ')'
      when son = max(son) over(partition by dad) then ') ' || dad_cond
      else condition
    end end_paren
  from q1
)
where id is not null
order by order1;

TXT
-----------------------
price less 10.25 AND
( category equal 2 OR
category equal 1 ) AND
( name contains test OR
in_stock equal 0 )

Do a LISTAGG to get everything on the same line.
Chris Saxon
April 12, 2017 - 12:33 pm UTC

Nice work Stew.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database