sqljsonsqlitegroup-bybetter-sqlite3

select query for one-to-many relations table

I have 2 tables. These two tables have one-to-many relations.

TABLE - A

column1 column2
1       label1
2       label2

TABLE - B

Bcolumn1 Bcolumn2 Bcolumn3
1        value1   value4
1        value2   value5
2        value3   value6

RESULT TABLE


column1 column2 json
1       label1  [{"Bcolumn":value1,"Bcolumn":value4},{"Bcolumn":value2,"Bcolumn":value5}]
2       label2  [{"Bcolumn":value3,"Bcolumn":value6}]

I want to get RESULT TABLE1 using TABLE - A and TABLE - B.

how can I get this result?

Thank you.

Solution

  • Use SQLite's JSON1 Extension functions:

    SELECT a.column1, a.column2,
       json_group_array(json_object('Bcolumn', b.Bcolumn2, 'Bcolumn', b.Bcolumn3)) json
FROM tableA a INNER JOIN tableB b
ON b.Bcolumn1 = a.column1
GROUP BY a.column1;

    See the demo.