CODEWITHSUNDEEP
arraysjsonsqlitejoinnode-sqlite3
J.F.
J.F.

Reputation: 15235

Get array with JOINed values

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?

Upvotes: 1

Views: 733

Answers (1)

forpas
forpas

Reputation: 164194

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.

Upvotes: 2

Related Questions