By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE test (data json);
INSERT INTO test VALUES ('{"identifiers":
[
{
"identifierType": "VIN",
"value": "L608"
},
{
"identifierType": "VIN",
"value": "L604"
}
]}');
CREATE TABLE
INSERT 0 1
SELECT "identifierType", value
FROM test
CROSS JOIN json_to_recordset(data->'identifiers') as x("identifierType" text, value text);
| identifierType | value |
|---|---|
| VIN | L608 |
| VIN | L604 |
SELECT 2