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?

Answer 1

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.