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.