PostgreSQL - Replace json array containing ids with descriptions

Question

I have working with Relational Databases for a while but I'm pretty new with PostgreSQL and handling Jsons in dbs. I have the following example:

CREATE TEMPORARY TABLE IF NOT EXISTS Tags
(
  Id INT,
  Description VARCHAR(100)
) 
ON COMMIT DELETE ROWS;

CREATE TEMPORARY TABLE IF NOT EXISTS EntitiesWithTags
(
  Id INT,
  jsonArray JSON
) 
ON COMMIT DELETE ROWS;

INSERT INTO Tags VALUES (1, 'Test');
INSERT INTO Tags VALUES (2, 'Hello');
INSERT INTO Tags VALUES (3, 'Goodbye');

INSERT INTO EntitiesWithTags VALUES(1, '[1, 2]');
INSERT INTO EntitiesWithTags VALUES(2, '[1, 3]');
INSERT INTO EntitiesWithTags VALUES(3, '[2]');

SELECT * FROM EntitiesWithTags;

The result of the select is this:

+----+-----------+
| id | jsonArray |
+----+-----------+
| 1  | [1, 2]    |
+----+-----------+
| 2  | [1, 3]    |
+----+-----------+
| 3  | [2]       |
+----+-----------+

But I would like to achive this:

+----+---------------------+
| id | jsonArray           |
+----+---------------------+
| 1  | ["Test", "Hello"]   |
+----+---------------------+
| 2  | ["Test", "Goodbye"] |
+----+---------------------+
| 3  | ["Hello"]           |
+----+---------------------+

In summary, I need to join the ids stored in a json array with its corresponding records in a regular table. I need to select the descriptions and not the ids in the array.

Thank you in advance.

user330315 · Accepted Answer · 2019-12-19 13:35:53Z

2

You need to unnest the elements, join the result and aggregate the descriptions back into an array:

SELECT et.id, json_agg(tg.description order by e.idx)
FROM entities_with_tags et
  cross join json_array_elements_text(et.jsonarray) with ordinality as e(tag_id, idx) 
  join tags tg on tg.id = e.tag_id::int
group by et.id
order by et.id

Online example

answered Dec 19, 2019 at 13:35

user330315

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

2 Comments

jmrivas Over a year ago

Thank you really much. That was exactly what I was looking for, I'm still getting used to Json handling functions in Postgre.

user330315 Over a year ago

Btw: jsonb is preferred over json these days (and the database is called Postgres, not Postgre)

Collectives™ on Stack Overflow

PostgreSQL - Replace json array containing ids with descriptions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related