Postgres 13

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"
            }
]}');


1 rows affected

SELECT "identifierType", value 
FROM test
CROSS JOIN json_to_recordset(data->'identifiers') as x("identifierType" text, value text);


identifierType
value


VIN
L608

VIN
L604


CREATE OR REPLACE FUNCTION convert_my_json(p_data json)
RETURNS TABLE (
  "identifierType" text,
  "value" text
)
AS $$
  SELECT * FROM json_to_recordset(p_data->'identifiers') as x("identifierType" text, value text);
$$
LANGUAGE SQL
IMMUTABLE;
SELECT * FROM convert_my_json('{"identifiers": 
 [
            {
                "identifierType": "VIN",
                "value": "L608"
            },
            {
                "identifierType": "VIN",
                "value": "L604"
            }
]}');


identifierType
value


VIN
L608

VIN
L604