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:

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:

For more information, see Oracle to PostgreSQL Migration.