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 tbl (words json);
insert into tbl values ('{"enterprise": [{"name": "bin", "type": 0, "props": {"score": 1, "id": "bin"}, "status": "supported"}, {"name": "bist", "type": 0, "props": {"score": 1, "id": "bin"}, "status": "unsupported"}], "private": [{"name": "hello", "type": 0, "props": {"score": 1, "id": "hello"}, "status": "supported"}, {"name": "hello", "type": 0, "props": {"score": 1, "id": "hello"}, "status": "supported"}]}');
1 rows affected
with cte(key, id, score, name, status) as (
select v1.key::text, (v.value -> 'props' -> 'id')::text, (v.value -> 'props' -> 'score')::text, v.value -> 'name' , (v.value -> 'status')::text
from tbl t cross join json_each(t.words) v1 cross join json_array_elements(v1.value) v
)
select jsonb_pretty(json_build_object(t2.key, t2.js)::jsonb)
from (select t1.key, json_agg(json_build_object('id', t1.id, 'score', t1.m, 'visible', false, 'words', t1.a, 'other', '[]'::json, 'exclude', t1.a1)) js
from (select c.key, c.id, max(c.score::int) m, json_agg(c.name) a, coalesce(json_agg(c.name) filter (where c.status = '"unsupported"'), '[]'::json) a1
from cte c group by c.key, c.id) t1 group by t1.key) t2
| jsonb_pretty |
|---|
| { "enterprise": [ { "id": ""bin"", "other": [ ], "score": 1, "words": [ "bin", "bist" ], "exclude": [ "bist" ], "visible": false } ] } |
| { "private": [ { "id": ""hello"", "other": [ ], "score": 1, "words": [ "hello", "hello" ], "exclude": [ ], "visible": false } ] } |