Postgres 16

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 json_tbl (data json);
INSERT INTO json_tbl VALUES 
  ('[{"name": "Toby", "occupation": "Software Engineer"},
     {"name": "Zaphod", "occupation": "Galactic President"}]')
, ('[{"name": "XXX", "occupation": "unknown"},
     {"name": "YYY", "occupation": "Galactic President"}]')
RETURNING *;

CREATE TABLE jsonb_tbl (data jsonb);
INSERT INTO jsonb_tbl VALUES 
  ('[{"name": "Toby", "occupation": "Software Engineer"},
     {"name": "Zaphod", "occupation": "Galactic President"}]')
, ('[{"name": "XXX", "occupation": "unknown"},
     {"name": "YYY", "occupation": "Galactic President"}]')
RETURNING *;

CREATE TABLE

data

[{"name": "Toby", "occupation": "Software Engineer"},
     {"name": "Zaphod", "occupation": "Galactic President"}]

[{"name": "XXX", "occupation": "unknown"},
     {"name": "YYY", "occupation": "Galactic President"}]

INSERT 0 2

CREATE TABLE

data

[{"name": "Toby", "occupation": "Software Engineer"}, {"name": "Zaphod", "occupation": "Galactic President"}]

[{"name": "XXX", "occupation": "unknown"}, {"name": "YYY", "occupation": "Galactic President"}]

INSERT 0 2

-- Postgres 9.2+: (demonstrating type json)
SELECT object
FROM   json_tbl
     , json_array_elements(data) AS object
WHERE  object->>'name' = 'Toby';

object

{"name": "Toby", "occupation": "Software Engineer"}

SELECT 1

-- Postgres 12+  (demonstrating type jsonb)
-- example only returns first match!
SELECT jsonb_path_query_first(data, '$[*] ? (@.name == "Toby")') AS object
FROM   jsonb_tbl
WHERE  data @> '[{"name": "Toby"}]'   -- optional, for index support
;

-- Alternative:
SELECT jsonb_path_query_first(data, '$[*] ? (@.name == "Toby")') AS object
FROM   jsonb_tbl
WHERE  data @@ '$[*].name == "Toby"'  -- equivalent alternative
;

object

{"name": "Toby", "occupation": "Software Engineer"}

SELECT 1

object

{"name": "Toby", "occupation": "Software Engineer"}

SELECT 1