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

Question

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'

Answer 1

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