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 cte AS (
SELECT '{"status": 200, "response": {"page": 1, "limit": 10, "total": 4, "orders": [{"id": 40201 }, {"id": 40111}]}}'::jsonb AS jsonobject
)
SELECT
jsonobject -> 'response' -> 'orders' -> 0
FROM cte
| ?column? |
|---|
| {"id": 40201} |
WITH cte AS (
SELECT '{"status": 200, "response": {"page": 1, "limit": 10, "total": 4, "orders": [{"id": 40201 }, {"id": 40111}]}}'::jsonb AS jsonobject
)
SELECT
elems.value
FROM
cte,
jsonb_array_elements(jsonobject -> 'response' -> 'orders') elems
WHERE
elems ->> 'id' = '40201'
| value |
|---|
| {"id": 40201} |