Postgres 12

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 things (
  id serial PRIMARY KEY
, thing text NOT NULL
, blueprint json NOT NULL DEFAULT '{}'
);
	
INSERT INTO things(thing, blueprint) VALUES
	('thing 1', '{}'),
	('thing 2', '{"1":{"name":"Iskapola","wight":"2"}}'),
	('thing 3', '{"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}'),
	('thing 4', '{"1":{"name":"Ulamir","weight":"1"},  "2":{"name":"Azamund","weight":"1"}}')
;


-- alternative with json ARRAY
CREATE TABLE things_arr (
  id serial PRIMARY KEY
, thing text NOT NULL
, blueprint json NOT NULL DEFAULT '{}'
);

INSERT INTO things_arr(thing, blueprint) VALUES
	('thing 1', '[]'),
	('thing 2', '[{"name":"Iskapola","wight":"2"}]'),
	('thing 3', '[{"name":"Azamund","weight":"3"}, {"name":"Iskapola","weight":"1"}]'),
	('thing 4', '[{"name":"Ulamir","weight":"1"},  {"name":"Azamund","weight":"1"}]');

CREATE TABLE



INSERT 0 4



CREATE TABLE



INSERT 0 4


SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM json_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );


id
thing
blueprint


3
thing 3
{"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}

4
thing 4
{"1":{"name":"Ulamir","weight":"1"},  "2":{"name":"Azamund","weight":"1"}}



SELECT 2


-- based on JSON array
SELECT t.*
FROM   things_arr t
WHERE  EXISTS (
   SELECT FROM json_array_elements(t.blueprint) elem
   WHERE  elem->>'name' ILIKE 'azamund'
   );


id
thing
blueprint


3
thing 3
[{"name":"Azamund","weight":"3"}, {"name":"Iskapola","weight":"1"}]

4
thing 4
[{"name":"Ulamir","weight":"1"},  {"name":"Azamund","weight":"1"}]



SELECT 2


-- your query
select *
from (select id, (json_each(blueprint)).value::json->>'name' as name
      from things) as t
where t.name ~* 'azamund';


id
name


3
Azamund

4
Azamund



SELECT 2

id	thing	blueprint
3	thing 3	{"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}
4	thing 4	{"1":{"name":"Ulamir","weight":"1"}, "2":{"name":"Azamund","weight":"1"}}

id	thing	blueprint
3	thing 3	[{"name":"Azamund","weight":"3"}, {"name":"Iskapola","weight":"1"}]
4	thing 4	[{"name":"Ulamir","weight":"1"}, {"name":"Azamund","weight":"1"}]