I'm using SQLite3 on NodeJS and I have a DB in memory for testing purposes like this, where there is a relation between table1 and table2. fk field in table2 is the id in table1.

TABLE1

TABLE2

When I run this query:

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

The result is

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

But I want something like:

[
  {
    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 using SQLite or should I use a no relational DB?

Thanks in advance.

CodePudding user response：

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.