Postgres join on json array values

Question

I have a table users as follows:

|  id  |        name         |
|  1   |  Marvin Hargraves   |
|  2   |  Lincoln Clayton    |
|  3   |  Frank Pearce       |

And a table posts where I got json column with attributes:

|  id   |        attributes       | content |
|  11   | {"user_ids":["1", "2"]} |  "xyz"  |
|  12   | {"user_ids":["1", "3"]} |  "xyz"  |

Id's in the array reference the user from users table. I need to display user names instead of IDs like:

    |  id   |                 users                   |
as an array
    |  11   | ["Marvin Hargraves", "Lincoln Clayton"] |
or string
    |  12   | "Marvin Hargraves, Frank Pearce"        |

I'm using PostgreSQL version 10.
I tried this query:

SELECT p.id, 
  (SELECT array_agg(array[u.name])
   FROM post AS p
   JOIN user u ON u.id = ANY(p.attributes->'user_ids')
   GROUP BY p.id) AS users
FROM post p

But I got the following error:

ERROR: op ANY/ALL (array) requires array on right side

json or jsonb? Do you just query id and users, or more columns in your real query? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 31, 2021 at 19:39

Erwin Brandstetter · Accepted Answer · 2021-03-31 19:47:19Z

2

I suggest to use an ARRAY constructor in a LATERAL subquery:

SELECT p.id, j.users
FROM   post p
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT u.name
      FROM   jsonb_array_elements_text(p.attributes->'user_ids') WITH ORDINALITY j(user_id, ord)
      JOIN   users u ON u.id = j.user_id::int
      ORDER  BY j.ord
      ) AS users
   ) j
;

db<>fiddle here

Note that null values in the array are ignored.

The CROSS JOIN never eliminates rows in this case because the ARRAY constructor always returns a row, even when the JSON array is empty or NULL.

answered Mar 31, 2021 at 19:24

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Jeremy · Accepted Answer · 2021-03-31 18:35:46Z

0

You will need to use jsonb_array_elements_text to unpack the jsonb array, join to users, and then aggregate it back together:

SELECT p.id, array_agg(name ORDER BY i) as users
FROM post p
-- Use WITH ORDINALITY to preserve the order of the original array
CROSS JOIN LATERAL jsonb_array_elements_text(p.attributes->'user_ids') WITH ORDINALITY AS j(user_id, i)
JOIN users ON users.id = j.user_id::int
GROUP BY p.id
;

Here's a fiddle.

answered Mar 31, 2021 at 18:35

Jeremy

6,77322 silver badges21 bronze badges

Collectives™ on Stack Overflow

Postgres join on json array values

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related