Get array with JOINed values

Question

I'm using SQLite3 on NodeJS and have a database in memory with a relation between table1 and table2. fk field in table2 is id in table1.

table1 :

id	value1	value2
1	v1_t1	v2_t1

table2 :

id	value1	fk
1	v1_t2	1
2	v2_t2	1

When I run this query:

SELECT * from table1 t1 INNER JOIN table2 t2 ON t2.fk=t1.id WHERE t1.id=1;

Result is :

[
  {
    id: 1,
    value1: v1_t2,
    fk:1
  },
  {
    id: 2,
    value1: v2_t2,
    fk:1
  }
]

But I want :

[
  {
    fk: 1,
    value1: "v1_t1",
    value2: "v2_t1",
    result: [
      {
        id: 1,
        value1: "v1_t2",
        fk: 1
      },
      {
        id: 2,
        value1: "v2_t2",
        fk: 1
      }
    ]
  }
]

Is this possible or should I use a non-relational database?

You posted the results that you get and your expected results as JSON objects which is not what the query returns. The query returns rows in tabular format and it resembles your expected results: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=3eb3aa46a8a5e2ae1bb41be46599abbb — forpas, Oct 17 '21 at 13:23
And there is a way (using [JSON extension](https://www.sqlite.org/json1.html) or whatever) to get desired output? — J.F., Oct 17 '21 at 13:30

forpas · Accepted Answer · 2021-10-17T13:58:59.630

2

You can use SQLite's JSON1 Extension functions:

SELECT json_object(
           'fk', t2.fk, 
           'value1', t1.value1, 
           'value2', t1.value2,
           'result',  
           json_group_array(json_object('id', t2.id, 'value1', t2.value1, 'fk', t2.fk))           
       ) col
FROM table1 t1 INNER JOIN table2 t2 
ON t2.fk = t1.id 
WHERE t1.id = 1;

See the demo.

edited Oct 17 '21 at 13:58

answered Oct 17 '21 at 13:50

forpas

160,666
10
38
76

Get array with JOINed values

1 Answers1