Postgres 11

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 ( id bigserial primary key, stuff jsonb not null );

insert into test (stuff) values
  ('{"country": "brazil"}'),
  ('{"city": "amsterdam"}'),
  ('{"count": 10}'), 
  ('{"counting": "crows", "pearl": "jam"}'),
  ('{"count down": "final", "count": "chocula"}');

CREATE TABLE



INSERT 0 5


-- Use jsonb_object_keys() to make each key into a row
select id, stuff, jsonb_object_keys(stuff) as key
from test;


id
stuff
key


1
{"country": "brazil"}
country

2
{"city": "amsterdam"}
city

3
{"count": 10}
count

4
{"pearl": "jam", "counting": "crows"}
pearl

4
{"pearl": "jam", "counting": "crows"}
counting

5
{"count": "chocula", "count down": "final"}
count

5
{"count": "chocula", "count down": "final"}
count down



SELECT 7


-- Get all the key/value pairs which match
select id, stuff, key, stuff->key as value
from (
  select id, stuff, jsonb_object_keys(stuff) as key
  from test
) pairs
where key like 'count%';


id
stuff
key
value


1
{"country": "brazil"}
country
"brazil"

3
{"count": 10}
count
10

4
{"pearl": "jam", "counting": "crows"}
counting
"crows"

5
{"count": "chocula", "count down": "final"}
count
"chocula"

5
{"count": "chocula", "count down": "final"}
count down
"final"



SELECT 5


-- Use distinct to get just the matching rows once.
select distinct id, stuff
from (
  select id, stuff, jsonb_object_keys(stuff) as key
  from test
) pairs
where key like 'count%';


id
stuff


1
{"country": "brazil"}

3
{"count": 10}

4
{"pearl": "jam", "counting": "crows"}

5
{"count": "chocula", "count down": "final"}



SELECT 4

id	stuff	key
1	{"country": "brazil"}	country
2	{"city": "amsterdam"}	city
3	{"count": 10}	count
4	{"pearl": "jam", "counting": "crows"}	pearl
4	{"pearl": "jam", "counting": "crows"}	counting
5	{"count": "chocula", "count down": "final"}	count
5	{"count": "chocula", "count down": "final"}	count down

id	stuff	key	value
1	{"country": "brazil"}	country	"brazil"
3	{"count": 10}	count	10
4	{"pearl": "jam", "counting": "crows"}	counting	"crows"
5	{"count": "chocula", "count down": "final"}	count	"chocula"
5	{"count": "chocula", "count down": "final"}	count down	"final"