Select Array Element by Property Value Postgres Jsonb

Question

I have an object stored in a jsonb column in my Postgres database.

The data in the column looks like this:

{
    "results": [
        {
            "createdByApp": "yes",
            "externalApps": [
                {
                    "modifiedByUser": "User-1",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test1"
                },
                {
                    "modifiedByUser": "User-2",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test2"
                },
                {
                    "modifiedByUser": "User-3",
                    "modifiedDate": "2023-09-12T08:43:10.779",
                    "name": "test3"
                }
            ],
            "modifiedByApp": "7572b20b-a594-48dd-8ab3-8bc2718a23bd"
        }
    ]
}

What I am trying to do is select the name of the externalApp that was modified by User-2.

I am able to pull the external apps as a json object by using:

select (act."MessageBody"::json->'results'->0)->>'externalApps' as extApps

This gives me the array of objects inside externalApps.

But from there I cannot find a way to select the name for modifiedByUser = User-2

I cannot guarantee the User-2 modified app is always the second element in the array. How can I search through the array and select a value if the modifiedByUser value matches?

Answer 1

In PostgreSQL 12 and above, you can use a jsonb_path_query() function: db<>fiddle demo

select jsonb_path_query( act."MessageBody"::jsonb
                        ,'$.results[*]
                           .externalApps[*]
                           ?(@.modifiedByUser==$var)
                           .name'
                        ,jsonb_build_object('var','User-2'))
from act;

jsonb_path_query
"test2"

JSONPath syntax offers both array wildcards [*] and a filter expression ?(). You can also pass the user you're looking for dynamically, as a variable from the outside.

---

Support for jsonb_array_elements() reaches all the way back to 9.4, so you can also:

select e['name'] as extApps
from act, jsonb_array_elements(act."MessageBody"::jsonb
                                ->'results'
                                ->0
                                ->'externalApps')_(e)
where e->>'modifiedByUser'='User-2';

extapps
"test2"

If you're not sure your externalApps will be the 0th element of results, you're going to need two of those:

select e2['name'] as extApps
from act, 
     jsonb_array_elements(act."MessageBody"::jsonb->'results')j1(e),
     jsonb_array_elements(e->'externalApps')j2(e2)
where e2->>'modifiedByUser'='User-2';

extapps
"test2"

Note these are all set-returning functions, so there are lots of lateral joins behind the scenes.