Oracle JSON_TABLE to PostgreSQL - how to search from the second hierarchical key in a JSON column

user_13518431 0
Viewed 1425

I'm trying to migrate Oracle 12c queries to Postgres11.5.

Here is the json:

{
   "cost": [{
    "spent": [{
      "ID": "HR",
      "spentamount": {
        "amount": 2000.0,
        "country": "US"
      }
    }]
  }],
  "time": [{
    "spent": [{
      "ID": "HR",
      "spentamount": {
        "amount": 308.91,
        "country": "US"
      }
    }]
  }]
}

Here is the query that has to be migrated to Postgres 11.5:

select js.*
from P_P_J r,
     json_table(r.P_D_J, '$.*[*]'
                 COLUMNS(NESTED PATH '$.spent[*]' 
                         COLUMNS(
                         ID VARCHAR2(100 CHAR) PATH '$.ID',
                         amount NUMBER(10,4) PATH '$.spentamount.amount',
                         country VARCHAR2(100 CHAR) PATH '$.spentamount.country'))
               ) js

The result: 

ID, amount, country
HR, 2000.0,US
HR,308.91,US

I have two questions here:

  1. What does $.*[*] mean?

  2. How can we migrate this query in Postgres so that it directly looks at 'spent' instead of navigating 'cost'->'spent' or 'time'->'spent'

jsonb database-migration postgresql oracle json
1 Answers

There is no direct replacement for json_table in Postgres. You will have to combine several calls to explode the JSON structure.

You didn't show us your expected output, but as far as I can tell, the following should do the same:

select e.item ->> 'ID' as id,
       (e.item #>> '{spentamount, amount}')::numeric as amount,
       e.item #>> '{spentamount, country}' as country
from p_p_j r
  cross join jsonb_each(r.p_d_j) as a(key, val)
  cross join lateral (
    select *
    from jsonb_array_elements(a.val) 
    where jsonb_typeof(a.val) = 'array'
  ) as s(element) 
  cross join jsonb_array_elements(s.element -> 'spent') as e(item)
;

The JSON path expression '$.*[*] means: iterate over all top-level keys, then iterate over all array elements found in there and the nested path '$.spent[*]' then again iterates over all array elements in there. These steps are reflected in the three JSON function calls that are needed to get there.

With Postgres 12, this would be a bit easier as this can be done with a single call to jsonb_path_query() which also use a JSON Path to access the elements using a very similar JSON path expression:

select e.item ->> 'ID' as id,
       (e.item #>> '{spentamount, amount}')::numeric as amount,
       e.item #>> '{spentamount, country}' as country
from p_p_j r
  cross join jsonb_path_query(r.p_d_j, '$.*[*].spent[*]') as e(item)
;

Online example

user_3303150
Related