Try to search your question here, if you can't find : Ask Any Question Now ?

Query a JSON array for multiple values using json1 in sqlite

HomeCategory: stackoverflowQuery a JSON array for multiple values using json1 in sqlite
john asked 1 week ago

I am trying to use the json1 extension to query my sqlite database and want to query a json array to match multiple values and return the entry that matches all the values.

Example entities:

Entity 1:

{
    EntityID: 123,
    tags: [tag1, tag2, tag3]
}

Entity 2:

{
    EntityID: 234,
    tags: [tag2, tag3]
}

In this example I want to query for tags that match multiple values. If my input is [tag1, tag2] then I want to return only Entity 1 whereas if my input is tag2 then I want to return both the entities.

The query I expected to work was

SELECT * 
FROM entities, json_each(tags) 
WHERE json_each.value IS "tag1" 
    AND json_each.value IS "tag2"

But this query fails all the cases. As to why it fails, I think that it tries to match each value of the array to be tag1 and tag2 which is not the case as each element has only 1 value. Is this valid or is there another explanation?

A query that did work for me in the very specific use case of checking [tag1, tag2] values and returning only Entity 1 is

SELECT * 
FROM entities, json_each(tags) 
WHERE json_each.value IS "tag1" OR "tag2"

I want to know as to why it works in the case above? The only thing that I can think of is that it checks each value of the array to be either tag1 or tag2 but I don’t understand why it returns Entity 1 which also has a value of tag3 and does not match the criteria. This case however fails in most other cases for example where I search [tag3, tag4], it returns both the entities in this case but it should return none. So this is definitely not the answer

I still have not been able to find the right way to query this efficiently and am looking for any suggestions.

Ideally if there is an even simpler way to query this then that would be much appreciated. What I have in mind is something where I give the query a list of values for the tags and it matches all of them without me having to use AND/OR operators

1 Answers
Best Answer
Matthias answered 1 week ago
Your Answer

15 + 11 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com