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?.
WITH mytable AS (
SELECT '{"territory_custom":[{"name_directory":"ЯПОНИЯ","id_ref_directory":38},{"name_directory":"ЯПОН","id_ref_directory":39},{"name_directory":"ЯП","id_ref_directory":40}]}'::jsonb AS mydata
)
SELECT * FROM mytable
| mydata |
|---|
| {"territory_custom": [{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38}, {"name_directory": "ЯПОН", "id_ref_directory": 39}, {"name_directory": "ЯП", "id_ref_directory": 40}]} |
SELECT 1
WITH mytable AS (
SELECT '{"territory_custom":[{"name_directory":"ЯПОНИЯ","id_ref_directory":38},{"name_directory":"ЯПОН","id_ref_directory":39},{"name_directory":"ЯП","id_ref_directory":40}]}'::jsonb AS mydata
)
SELECT
*
FROM mytable,
jsonb_array_elements(mydata -> 'territory_custom') AS elems
| mydata | value |
|---|---|
| {"territory_custom": [{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38}, {"name_directory": "ЯПОН", "id_ref_directory": 39}, {"name_directory": "ЯП", "id_ref_directory": 40}]} | {"name_directory": "ЯПОНИЯ", "id_ref_directory": 38} |
| {"territory_custom": [{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38}, {"name_directory": "ЯПОН", "id_ref_directory": 39}, {"name_directory": "ЯП", "id_ref_directory": 40}]} | {"name_directory": "ЯПОН", "id_ref_directory": 39} |
| {"territory_custom": [{"name_directory": "ЯПОНИЯ", "id_ref_directory": 38}, {"name_directory": "ЯПОН", "id_ref_directory": 39}, {"name_directory": "ЯП", "id_ref_directory": 40}]} | {"name_directory": "ЯП", "id_ref_directory": 40} |
SELECT 3
WITH mytable AS (
SELECT '{"territory_custom":[{"name_directory":"ЯПОНИЯ","id_ref_directory":38},{"name_directory":"ЯПОН","id_ref_directory":39},{"name_directory":"ЯП","id_ref_directory":40}]}'::jsonb AS mydata
)
SELECT
jsonb_agg(elems -> 'id_ref_directory')
FROM mytable,
jsonb_array_elements(mydata -> 'territory_custom') AS elems
| jsonb_agg |
|---|
| [38, 39, 40] |
SELECT 1
WITH mytable AS (
SELECT '{"territory_custom":[{"name_directory":"ЯПОНИЯ","id_ref_directory":38},{"name_directory":"ЯПОН","id_ref_directory":39},{"name_directory":"ЯП","id_ref_directory":40}]}'::jsonb AS mydata
)
SELECT
array_agg((elems ->> 'id_ref_directory')::int)
FROM mytable,
jsonb_array_elements(mydata -> 'territory_custom') AS elems
| array_agg |
|---|
| {38,39,40} |
SELECT 1