Count object aggregate in Postgres JSON Laravel 8

sixfive 0
Viewed 22

I have this JSON field on each row of the table that records who was involved in a safety incident in a risk management tool, and what their role was in that incident.

'roles':[
    {
        "user":
        {
            "user_id": "c4456c9c-880b-4d1f-8066-47d77e2b6eac",
            "name": "James Dean",
        },
        "role": "170"
    },
    {
        "user":
        {
            "user_id": "cd91f708-12cd-4541-9022-6gf6a44a2e43",
            "name": "John Smith",
        },
        "role": "172"
    }
]

I need a summary of the role by user. The number of roles is unknown, although is usually between 2-6 roles. The end result of this report will look something like:

e.g.

User Role 170 Role 171 Role 172
James Dean 1 0 0
John Smith 0 0 1

Currently we pull the entire object and process it in PHP but I'd like to push as much work back on to postgres as possible so the agregations are faster.

We are using Laravel 8 and Postgres 12.8 - the column is json not jsonb (I'm not sure if that matters) - thanks!

laravel-8 eloquent postgresql
1 Answers

I wrote sample for you:

CREATE TABLE testdata (
    jsondata json NULL
);

INSERT INTO testdata (jsondata) VALUES('{
    "roles": [
        {
            "user": {
                "user_id": "c4456c9c-880b-4d1f-8066-47d77e2b6eac",
                "name": "James Dean"
            },
            "role": "170"
        },
        {
            "user": {
                "user_id": "cd91f708-12cd-4541-9022-6gf6a44a2e43",
                "name": "John Smith"
            },
            "role": "172"
        }
    ]
}
'::json);

If the number of roll columns is known in advance, then use this query:

select 
    t1.names, 
    case when t1.roles = '170' then 1 else 0 end as "Role 170", 
    case when t1.roles = '171' then 1 else 0 end as "Role 171", 
    case when t1.roles = '172' then 1 else 0 end as "Role 172" 
from (
    select 
        json_array_elements((jsondata->>'roles')::json)->>'role' as roles, 
        ((json_array_elements((jsondata->>'roles')::json)->>'user')::json)->>'name' as names
    from testdata
) t1 

Result: 
names       Role 170    Role 171    Role 172
James Dean  1           0           0
John Smith  0           0           1

If the number of roll columns is not known in advance, then you can use postgress pivot table (cross table) functions. Before using cross table functions you must install on postgres extension tablefunc

Ramin Faracov0
Related