PostgreSQL: get all json object values per row

Question

I have a games table with players row. The players column is a jsonb in the form of: {"digit": [id, id, id, ...], ...}:

select id, players from games;
 id  |           players           
-----+-----------------------------
 236 | {"10": [27, 23, 25]}
 238 | {"7": [22]}
 239 | {"1": [], "2": [], "3": []}
 237 | {"1": []}

I would like, for each game id (row) have an array of ids, something like this:

  id       just_arrays
-----+-----------------------------
 236 | [27, 23, 25]
 238 | [22]
 239 | []
 237 | []

I would then like to build a document where ids will be changed to player's names (which I have in user table):

  id              document
-----+-----------------------------
 236 | Player Name1 Player Name2 Player Name3
 238 | Player Name4
 239 | 
 237 |

How I can achieve that? I've tried something with jsonb_each() but without luck.

in value {"10": [27, 23, 25]} what is the significance of "10". whether it is fixed or any logic is there — Akhilesh Mishra
– Akhilesh Mishra, Commented Aug 2, 2021 at 9:58
Just a name of the play court (mostly a number). I do not need this key in this context. It is not important. — Sławek
– Sławek, Commented Aug 2, 2021 at 10:09
This would be so much easier with a properly normalized data model — user330315
– user330315, Commented Aug 2, 2021 at 10:11
@a_horse_with_no_name well I have structure like this at the moment. The version is PostgreSQL 13.3. — Sławek
– Sławek, Commented Aug 2, 2021 at 10:15

score 4 · Accepted Answer · 2021-08-02 10:23:48Z

4

You can use a JSON path function for this:

select id, jsonb_path_query_array(players, '$.*[*]') as players.
from games

If you need to join that result to another table you need to first unnest the arrays, then join to that result and aggregate back:

select g.id, string_agg(u.name, ' ')
from games g
  left join jsonb_array_elements_text(jsonb_path_query_array(players, '$.*[*]')) as gp(pid) on true
  left join users u on u.id = gp.pid::int
group by g.id
order by g.id;

Online example

edited Aug 2, 2021 at 10:23

answered Aug 2, 2021 at 10:16

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: get all json object values per row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related