0

I am looking for the Sqlite syntax to join multiple JSON columns, in the same table. I create a table like:-

SQL = "CREATE TABLE BranchStock (branch VARCHAR, oldstock JSON, newstock JSON)"
....

I then insert a few items like:-

INSERT INTO BranchStock VALUES('Melbourne','[{"catnumber":"ABC123","instock":5},{"catnumber":"BCD321","instock":3}]','[{"catnumber":"ABC123","instock":1},{"catnumber":"BCD321","instock":5}]');
INSERT INTO BranchStock VALUES('Sydney','[{"catnumber":"ABC123","instock":7},{"catnumber":"XYZ567","instock":0}]','[{"catnumber":"ABC123","instock":5},{"catnumber":"XYZ567","instock":3}]');
....

Now I'm looking for the search sql to list which branches have the catnumber='ABC123' newstock or oldstock, and the instock amounts. Perhaps something like this:-

SELECT * FROM BranchStock 
   JOIN json_each(oldstock) ON json_valid(oldstock)
   JOIN json_each(newstock) ON json_valid(newstock)
   WHERE json_extract(value, '$.name')='ABC123');

The above works for one JOIN, but when I implement the second JOIN I get a syntax error.

Does anyone know the correct syntax? Thanks in advance.

oz123
  • 27,559
  • 27
  • 125
  • 187
42LeapsOfFaith
  • 146
  • 1
  • 9
  • Rethink your database design. Tables with one row per item work far, far better than trying to store multiple items in a single cell. – Shawn Nov 28 '18 at 18:50

1 Answers1

0

You weren't specific about the "syntax" error, so I gave it a whirl.

This SQL gives a syntax error on the last ) in the WHERE clause. Once that's fixed it complains about ambiguous column name, which can be fixed by aliasing the json_eachs. There's more complaining, but it's an iterative process.

I got (my interpretation of) it to work, and it was fun! So keep at it.

HINT: I changed (both) joins like so 

LEFT JOIN json_each(oldstock) os ON json_valid(oldstock) and json_extract(os.value,'$.catnumber') = 'ABC123'

[I won't argue with @Shawn's comment, but I have used this approach and it worked for me and so far has saved a lot of refactoring!]

DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15
  • Thanks. The "last )" was a victim of cut & paste into here (it was late). Having a variable number of columns (indexable, within a table) solves are few a problems now and then. Thanks for the tip on aliasing. Did you get a version to work completely? Share? – 42LeapsOfFaith Nov 28 '18 at 22:49
  • added hint to the answer. – DinoCoderSaurus Nov 29 '18 at 01:52