select query for one-to-many relations table

Question

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.

score 1 · Accepted Answer · answered Feb 16 '22 at 21:12

1

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.

answered Feb 16 '22 at 21:12

forpas

160,666
10
38
76

@Yash this is a different requirement for which you should ask a new question and not update the current question because this way yo invalidate the answers that you already received. – forpas Feb 17 '22 at 16:05
@Yash this is not the same requirement because your expected results are not a json array. It consists of concatenated values and because of the surrounding square brackets it looks like (but it is not) a json array. So, the solution is different in this case. Also, as I mentioned in my previous comment, editing a question and asking for something different or new invalidates the existing answers and it is discouraged. – forpas Feb 17 '22 at 18:42

Philippe · Answer 2 · 2022-02-16T21:25:06.863

1

What you are looking for in sqlite is the group_concat() function. It's tricky, cause you have he values you want to concat in 2 different columns. Basically you can do the following

select
    a.column1
  , a.column2
  , '[{' || group_concat('"Bcolumn":' || b.bcolumn2 || '"Bcolumn":' || b.bcolumn3,'};{') || '}]' as json_output
from tablea a
inner join tableb b on
  a.column1 = b.bcolumn1
group by
    a.column1
  , a.column2
;

I tested this solution with MSSQL 2019 and string_agg(), but from the documentation (https://www.sqlitetutorial.net/sqlite-group_concat/) this should work just as well in sqlite.

The trick is to use '};{' as separator, because like this, you will only have to care about the opening and closing brackets and nothing in the middle.

edited Feb 16 '22 at 21:25

answered Feb 16 '22 at 21:13

Philippe

91
7

SQLite supports json aggregate functions to produce json objects. There is no need for concatenations and group_concat(). – forpas Feb 16 '22 at 21:30
1

You're right, @forpas. I guess in the end it boils down to whether or not you can load extensions or not, though. Depending on server settings, you're stuck with native functions. – Philippe Feb 16 '22 at 21:35
You say this trick '};{' as separator otherwise how to do? can you share an example? – Yash Feb 18 '22 at 08:01

select query for one-to-many relations table

2 Answers2