1

I have a SQLite database which contains a table that has a text column. In this column I store a JSON array of strings.

This is the table, shortened to include only what's relevant:

CREATE TABLE "recipePreview" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL, "intro" TEXT,
"tags" TEXT
)

And some sample data (hand written might contain syntax errors...):

INSERT INTO recipePreview (id, name, tags) VALUES (NULL, "My recipe", '["glutenFree","raw","naturallySweetened","vegan"]')
INSERT INTO recipePreview (id, name, tags) VALUES (NULL, "My other recipe", '["aquafaba","glutenFree","vegan"]')

I want to filter these rows based on contents of the JSON array in the tags column. For example; a query that returns all of the recipes that are tagged with raw. I can do that using this query:

SELECT * FROM recipePreview, json_tree(recipePreview.tags, '$')
    WHERE value = "a tag"

What I haven't been able to figure out though is if there is a way to have an AND in my WHERE claus. Ie. something that would look something like this:

SELECT * FROM articles, json_tree(recipePreview.tags, '$')
    WHERE value = "a tag" AND value = "another tag"

I don't want to just do a string compare (using LIKE) because one tag might contain parts of another tag. When searching for recipes tagged with candy I don't want to get hits for candy cotton (contrived example).

OR queries work with the following approach as json_tree actually creates "many rows" for each entry in the JSON array.

Is this somehow possible using the JSON1 extension?

oz123
  • 27,559
  • 27
  • 125
  • 187
iamsim.me
  • 560
  • 4
  • 17
  • Can you include your table definition, sample rows (preferrably as insert statements for easy copy and pasting), and what you want the results to be? – Shawn May 14 '19 at 17:23
  • Updated the question to include more stuff! – iamsim.me May 15 '19 at 06:15
  • Do you want all rows that contain any of the values, or only rows that contain ALL the values? What would be the result of the above example if the search was "raw","vegan"? – DinoCoderSaurus May 15 '19 at 10:33
  • An `AND` query means getting the rows that match _both_ criteria. – iamsim.me May 16 '19 at 06:40

1 Answers1

1

You can use LIKE. Exploit the fact that within the JSON column, each tag is enclosed in ".

WHERE LIKE '%candy%' will return candy and cotton candy.
WHERE LIKE '%"candy"%' will only return candy.

Nesting notwithstanding, you don't even need to use json_tree, since the tags column is ostensibly TEXT. If it is nested, you could select rows WHERE key = 'tags' and (value like '%"a tag"%' AND value like '%"another tag"%'

DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15
  • As I said, string matching won't work well since a shorter tag might be included in a longer tag. – iamsim.me May 15 '19 at 06:02
  • answer emended. – DinoCoderSaurus May 16 '19 at 13:16
  • Yes. Seems like I accidentally edited that section away before. I realize that's possible. It just _feels_ so hacky :P Which is why I asked specifically about how to do it using the `JSON1` extensions functions. But maybe this is the most pragmatic way. I'll accept the answer for now. Should someone come along with a solution using the extension that'd be cool. Thanks! – iamsim.me May 17 '19 at 15:34