2

In my database I have a table relations with a column relation_ids containing the IDs of users (user_id). This takes the form of an array with many IDs possible, e.g.:

{111,112,156,4465}

I have another table names containing information on users such as user_id, first_name, last_name etc.

I would like to create an SQL query to return all rows from relations with all columns, but append the array column relation_ids with first_name from the names table substituted for IDs.

Is it possible as some kind of subquery?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Charlie
  • 85
  • 9
  • Part of your question might be a duplicate of https://stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql It would be helpful to see sample data and a desired result set. – Jeff Breadner Jan 18 '19 at 18:28
  • 1
    Please **[EDIT]** your question and add some [sample data](https://ozh.github.io/ascii-tables/) and the expected output based on that data. [Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **[edit]** your question - do **not** post code or additional information in comments. –  Jan 18 '19 at 18:31

2 Answers2

6

Assuming you want to preserve the order in the array - first names listed in the same order as IDs in the original relation_ids.

I suggest an ARRAY constructor over a correlated subquery with unnest() and WITH ORDINALITY, joined to the names table, like:

SELECT r.*
     , ARRAY (
          SELECT n.first_name
          FROM   unnest(r.relation_ids) WITH ORDINALITY AS a(user_id, ord)
          JOIN   names n ON n.user_id = a.user_id
          ORDER  BY a.ord
          )  AS first_names
FROM   relations r;

This query preserves all rows from relations in any case.

Corner cases to note:

  1. A NULL value in relation_ids (for the whole column) is translated to an empty array. (Same as empty array in the source.)
  2. NULL elements are silently dropped from the array.

You might want to define desired behavior if those corner cases are possible ...

fiddle

Related:

Considered a normalized db design:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Can you suggest something that would work for athena queries? I tried running the same query in athena but it gave the error no viable alternative at input '(array ( select' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 783d65ea-d9dd-4cbf-8d9f-8f9ff6e32eab; proxy: null) – Akshat Choudhary Mar 11 '21 at 11:10
  • This answer is for PostgreSQL. You might ask Amazon about their product Athena. – Erwin Brandstetter Mar 11 '21 at 16:47
0

This will get you all the columns and rows from Relations with first_name appended from the Names table.

Select Relations.relation_ids, Names.user_id, Names.first_name From Relations
Inner Join Names On Relations.user_id=Names.user_id
MapinTX
  • 127
  • 2
  • 12
  • I get the following error: ERROR: operator does not exist: jsonb = text LINE 2: ...er Join Names On Relations.user_id=Names..... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. – Charlie Feb 11 '19 at 11:00