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 (
rec_id int generated by default as identity,
usr_id int unique,
format text[],
syn_data jsonb,
generation_date timestamptz);
insert into test values
( 2,
144,
'{audio,image,text}',
'{ "1":{"syn_idx":[724966,125940,727242],
"score":5.0,
"custom_score":1.0},
"2":{"syn_idx":[119294,119321],
"score":5.0,
"custom_score":1.0},
"3":{"syn_idx":[654145],
"score":5.0,
"custom_score":1.0},
"4":{"syn_idx":[617595,348300],
"score":5.0,
"custom_score":1.0}}',
'2023-07-19 06:29:51.584859')
returning *;
CREATE TABLE
| rec_id | usr_id | format | syn_data | generation_date |
|---|---|---|---|---|
| 2 | 144 | {audio,image,text} | {"1": {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0}, "2": {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0}, "3": {"score": 5.0, "syn_idx": [654145], "custom_score": 1.0}, "4": {"score": 5.0, "syn_idx": [617595, 348300], "custom_score": 1.0}} | 2023-07-19 06:29:51.584859+00 |
INSERT 0 1
select rec_id
,syn_data - translate(
jsonb_path_query_array( syn_data
,'$.keyvalue().key
?(@.double()<$min ||
@.double()>$max)'
,jsonb_build_object('min',0,
'max',2))::text
,'[]', '{}')::text[]
from test;
| rec_id | ?column? |
|---|---|
| 2 | {"1": {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0}, "2": {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0}} |
SELECT 1
select rec_id
,syn_data - remove_these
from test,
lateral (select array_agg(remove_these::text) as remove_these
from jsonb_path_query( syn_data
,'$.keyvalue().key.double()
?(@ < $min ||
@ > $max)'
,jsonb_build_object('min',0,
'max',2))
as g1(remove_these));
| rec_id | ?column? |
|---|---|
| 2 | {"1": {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0}, "2": {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0}} |
SELECT 1
select rec_id
,syn_data - remove_these
from test,
lateral (select array_agg(remove_these::text) as remove_these
from (select jsonb_object_keys(syn_data) as remove_these offset 2) as g1
) as g2;
| rec_id | ?column? |
|---|---|
| 2 | {"1": {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0}, "2": {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0}} |
SELECT 1
select rec_id,jsonb_object_agg(k,v)
from test,
lateral (select * from jsonb_each(syn_data) limit 2)_(k,v)
group by rec_id;
| rec_id | jsonb_object_agg |
|---|---|
| 2 | {"1": {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0}, "2": {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0}} |
SELECT 1
select rec_id
,jsonb_path_query( syn_data
,'$.keyvalue()
?(@.key.double()>=$min &&
@.key.double()<=$max)
.value'
,jsonb_build_object('min',0,
'max',2))
from test;
| rec_id | jsonb_path_query |
|---|---|
| 2 | {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0} |
| 2 | {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0} |
SELECT 2
select rec_id,nth_syn_data_value
from test,jsonb_each(syn_data)_(syn_data_n,nth_syn_data_value)
where syn_data_n::int between 0 and 2
order by syn_data_n::int;
| rec_id | nth_syn_data_value |
|---|---|
| 2 | {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0} |
| 2 | {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0} |
SELECT 2
select rec_id,syn_data->(generate_series(1,2)::text)
from test;
| rec_id | ?column? |
|---|---|
| 2 | {"score": 5.0, "syn_idx": [724966, 125940, 727242], "custom_score": 1.0} |
| 2 | {"score": 5.0, "syn_idx": [119294, 119321], "custom_score": 1.0} |
SELECT 2