SQL: Group By and create a new json column-CodePudding

I have a table

-----------------------------------------------------
|  id  |  name  |                result             |
-----------------------------------------------------
| 1    | Maria  |    {type: download, status: pass} |
| 1    | Maria  |    {type: login, status: fail}    |
| 2    | Tony   |    {type: download, status: fail} |
| 3    | Kate   |    {type: download, status: pass} |
| 3    | Kate   |    {type: login, status: pass}    |
| 3    | Kate   |    {type: logout, status: pass}   |
| 4    | Tom    |    {type: download, status: pass} |
-----------------------------------------------------

And I want to try to get this but not sure that it possible:

-----------------------------------------------------------
|  id  |  name  |  download |           action            |
-----------------------------------------------------------
| 1    | Maria  |    pass   |      {login: fail}          |
| 2    | Tony   |    fail   |                             |
| 3    | Kate   |    pass   | {login: pass, logout: pass} |
| 4    | Tom    |    pass   |                             |
-----------------------------------------------------------

I found way to create a new simple column:

SELECT id, name
       results.download AS download
FROM
(
    SELECT id, name jsonb_agg(result) AS results
        FROM my_table
        GROUP BY id, name
) AS r
ORDER BY id, name;

but also I want to move login and logout field to action column. Is it possible?

CodePudding user response：

The formatting is not exactly as you want but we have the right information in the right place.

create table my_table (
id int,
name varchar(10),
result text
);
insert into my_table values
(1,'Maria','{ "type": "download", "status": "pass"}'),
(1,'Maria','{ "type": "login", "status": "fail"}'),
(2,'Tony','{ "type": "download", "status": "fail"}'),
(3,'Kate','{ "type": "download", "status": "pass"}'),
(3,'Kate','{ "type": "login", "status": "pass"}'),
(3,'Kate','{ "type": "logout", "status": "pass"}'),
(4,'Tom','{ "type": "download", "status": "pass"}');

✓

7 rows affected

SELECT id, name
       results.download AS download
FROM
(
    SELECT id, name jsonb_agg(result) AS results
        FROM my_table
        GROUP BY id, name
) AS r
ORDER BY id, name;

ERROR:  syntax error at or near "."

LINE 2: results.download AS download ^

select
  id,
  name,
  result::json->'type' AS type,
  result::json->'status' AS status
FROM my_table

id | name  | type       | status
-: | :---- | :--------- | :-----
 1 | Maria | "download" | "pass"
 1 | Maria | "login"    | "fail"
 2 | Tony  | "download" | "fail"
 3 | Kate  | "download" | "pass"
 3 | Kate  | "login"    | "pass"
 3 | Kate  | "logout"   | "pass"
 4 | Tom   | "download" | "pass"

with cte as
(select 
  id,
  name,
  case when cast(result::json->'type' as text) = '"download"' 
    then cast(result::json->'status' as text) end as "download" ,
  case when cast(result::json->'type' as text)  in ('"login"','"logout"') 
  then concat(result::json->'type',': ',result::json->'status') end as "action"
from my_table)
select 
  id, 
  name,
  max(download),
  string_agg(action,', ') "action"
from cte
group by
  id, 
  name
order by id

id | name  | max    | action                           
-: | :---- | :----- | :--------------------------------
 1 | Maria | "pass" | "login": "fail"                  
 2 | Tony  | "fail" | null                             
 3 | Kate  | "pass" | "login": "pass", "logout": "pass"
 4 | Tom   | "pass" | null

db<>fiddle here

CodePudding user response：

You can aggregate the downloads and the "other" items into two separate columns:

with data as (
  select id, 
         name, 
         jsonb_object_agg('download', result ->> 'status') filter (where result ->> 'type' = 'download') as download,
         jsonb_object_agg(result ->> 'type', result ->> 'status') filter (where result ->> 'type' <> 'download') as result
  from my_table
  group by id, name
)
select id, name,
       download ->> 'download' as download,
       result
from data;

The CTE returns this:

id | name  | download             | results                            
--- ------- ---------------------- ------------------------------------
 1 | Maria | {"download": "pass"} | {"login": "fail"}                  
 2 | Tony  | {"download": "fail"} |                                    
 3 | Kate  | {"download": "pass"} | {"login": "pass", "logout": "pass"}
 4 | Tom   | {"download": "pass"} |

And the final then just extracts the value for the download key.

Online example