PostgreSQL JSON Functions

PostgreSQL already supports the functions json_to_recordset(json) and jsonb_to_recordset(jsonb). These functions are part of the "Processing and Creating JSON Data" category in PostgreSQL's JSON functions.

These functions convert JSON arrays of objects into sets of records, which is perfect for your use case with JSON data like:

PYTHONCopied
[{"value": 3, "type": "first"}, {"value": 5, "type": "second"}, ...]

Working with JSON Arrays in PostgreSQL

Here's how you can use jsonb_to_recordset to extract values from your JSON array:

SQLCopied
SELECT value, type
FROM jsonb_to_recordset('[
  {"value": 3, "type": "first"}, 
  {"value": 5, "type": "second"}
]'::jsonb) AS x(value int, type text);

This would return:

 value | type
-------+--------
     3 | first
     5 | second

Filtering by Type

If you need to filter by the "type" field, you can add a WHERE clause:

SQLCopied
SELECT value, type
FROM jsonb_to_recordset('[
  {"value": 3, "type": "first"}, 
  {"value": 5, "type": "second"}
]'::jsonb) AS x(value int, type text)
WHERE type = 'first';

Working with JSON in a Table Column

If your JSON is stored in a table column, you can use it like this:

SQLCopied
SELECT j.value, j.type
FROM your_table,
     jsonb_to_recordset(your_json_column) AS j(value int, type text)
WHERE j.type = 'first';

PostgreSQL 17 Enhancements

PostgreSQL 17 has added new SQL/JSON query functions that can further enhance your JSON processing capabilities:

• JSON_EXISTS()
• JSON_QUERY()
• JSON_VALUE()

Additionally, PostgreSQL 17 introduced new jsonpath methods to convert JSON values to other data types, such as:

• .bigint()
• .boolean()
• .integer()
• .string()
• And several others

These new functions can provide alternative approaches to working with your JSON data.