JSON_TABLE Function - Oracle to PostgreSQL Migration

In Oracle the JSON_TABLE function allows you to extract content from JSON using the specified JPATH expression and produce table rows and columns. In PostgreSQL use can use the LATERAL clause and JSON expressions.

Consider the following sample table:

  CREATE TABLE cities_json (id INT, data VARCHAR(100));
 
  -- Sample JSON rows
  INSERT INTO cities_json VALUES (1, '{"Cities":[{"Name":"Seville", "Country":"Spain"}, 
    {"Name":"Warsaw", "Country":"Poland"}]}');
  INSERT INTO cities_json VALUES (2, '{"Cities":[{"Name":"Boston", "Country":"United States"}]}');
  COMMIT;

Oracle:

  SELECT id, js.rown, js.city_name, js.city_country
  FROM 
    cities_json,
    JSON_TABLE(cities_json.data, '$.Cities[*]'
       COLUMNS (
           rown FOR ORDINALITY,
           city_name VARCHAR(30) PATH '$.Name',
           city_country VARCHAR(30) PATH '$.Country')
    ) js;

Result:

ID ROWN CITY_NAME CITY_COUNTRY
1 1 Seville Spain
1 2 Warsaw Poland
2 1 Boston United States

PostgreSQL:

  SELECT id, js.rown, js.city_name, js.city_country
  FROM 
    cities_json,
    LATERAL 
    (SELECT 
        ROW_NUMBER() OVER () AS rown,
        (json_data->>'Name')::VARCHAR(30) AS city_name,
        (json_data->>'Country')::VARCHAR(30) AS city_country
     FROM (SELECT jsonb_path_query(cities_json.data::jsonb, '$.Cities[*]') json_data) t
    ) js;

Result:

id rown city_name city_country
1 1 Seville Spain
1 2 Warsaw Poland
2 1 Boston United States

For more information, see Oracle to PostgreSQL Migration.