Retrieve multiple fields from jsonb array in postgres

Question

I'm wondering if I can retrieve multiple fields from postgres jsonb column. The jsonb dats format is something like:

{
    ...
    "teamId": 12,
    "teamName": "super team",
    "teamMembers": [
        {
            ...
            "memberId": 34,
            "externalId": 7783,
            "firstname": "John",
            "lastname": "Doe"
            ...
        }
    ]
    ...
}

What I'm trying to do is to fetch only teamId, memberId, externalId fields skipping all other fields. This database is really huge so I'm trying to optimise this query to speed it up and reduce network load.

With a jsonpath plus I could fetch members fields with something like thus: $.teamMembers.[teamId, externalId] but it seems postgres doesn't support such a syntax.

Does this answer your question? Query for multiple array elements in Postgres JSONB column — Luuk
– Luuk, Commented Jan 18, 2024 at 18:01
Do you want to fetch the team members as a relation (with 3 columns)? Or do you want to fetch them as a JSON (array of) object(s), per row in the input relation? Or something else? — Bergi
– Bergi, Commented Jan 18, 2024 at 18:02
@tomredfern Why [jsonpath-plus]? Postgres doesn't support that, it seems unrelated to the question. — Bergi
– Bergi, Commented Mar 7, 2024 at 11:36

Stefanov.sm · Accepted Answer · 2024-01-18 19:09:22Z

Flatten the JSONB column using jsonb_array_elements and a lateral join. Then do a trivial select.
The t CTE is a mimic of the real table.

with t(id, jsonfield) as
(
 values
 (1, '{
    "teamId": 12,
    "teamName": "super team",
    "teamMembers": [
        {"memberId": 34, "externalId": 7783, "firstname": "John", "lastname": "Doe"},
        {"memberId": 134, "externalId": 17783, "firstname": "Kate", "lastname": "Doe"}
    ]
   }'::jsonb),
 (2, '{
    "teamId": 22,
    "teamName": "super team",
    "teamMembers": [
        {"memberId": 35, "externalId": 7784, "firstname": "Peter", "lastname": "Doe"},
        {"memberId": 135, "externalId": 17784, "firstname": "Ada", "lastname": "Doe"}
    ]
   }'::jsonb)
)
select (jsonfield ->>'teamId')::integer as teamid, 
       (jarr ->> 'memberId')::integer as memberid, 
       (jarr ->> 'externalId')::integer as externalId
from t, lateral jsonb_array_elements(jsonfield -> 'teamMembers') jarr;

teamid	memberid	externalid
12	34	7783
12	134	17783
22	35	7784
22	135	17784

This approach is a possible PostgreSQL's functional equivalent of JSON_TABLE in Oracle/MySQL and OPENJSON in MSSQL. Another one as @Bergi commented is to use jsonb_to_recordset.

Collectives™ on Stack Overflow

Retrieve multiple fields from jsonb array in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related