Postgres 12

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 '{"user_1":{"permissions":["edit","view"]},"user_2":{"permissions":[]},"user_3":{"permissions":["view"]}}'::jsonb AS mydata
)

SELECT
    *
FROM
    mytable,
    jsonb_each(mydata)

mydata
key
value

{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}
user_1
{"permissions": ["edit", "view"]}

{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}
user_2
{"permissions": []}

{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}
user_3
{"permissions": ["view"]}

SELECT 3

WITH mytable AS (
    SELECT '{"user_1":{"permissions":["edit","view"]},"user_2":{"permissions":[]},"user_3":{"permissions":["view"]}}'::jsonb AS mydata
)

SELECT
    jsonb_build_object('login', key)
FROM
    mytable,
    jsonb_each(mydata)

jsonb_build_object

{"login": "user_1"}

{"login": "user_2"}

{"login": "user_3"}

SELECT 3

WITH mytable AS (
    SELECT '{"user_1":{"permissions":["edit","view"]},"user_2":{"permissions":[]},"user_3":{"permissions":["view"]}}'::jsonb AS mydata
)

SELECT
    jsonb_build_object('login', key) || value
FROM
    mytable,
    jsonb_each(mydata)

?column?

{"login": "user_1", "permissions": ["edit", "view"]}

{"login": "user_2", "permissions": []}

{"login": "user_3", "permissions": ["view"]}

SELECT 3

WITH mytable AS (
    SELECT '{"user_1":{"permissions":["edit","view"]},"user_2":{"permissions":[]},"user_3":{"permissions":["view"]}}'::jsonb AS mydata
)

SELECT
    jsonb_agg(jsonb_build_object('login', key) || value)
FROM
    mytable,
    jsonb_each(mydata)

jsonb_agg

[{"login": "user_1", "permissions": ["edit", "view"]}, {"login": "user_2", "permissions": []}, {"login": "user_3", "permissions": ["view"]}]

SELECT 1

mydata	key	value
{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}	user_1	{"permissions": ["edit", "view"]}
{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}	user_2	{"permissions": []}
{"user_1": {"permissions": ["edit", "view"]}, "user_2": {"permissions": []}, "user_3": {"permissions": ["view"]}}	user_3	{"permissions": ["view"]}