Filtering multiple items in json array with sqlite json_each

Question

I have an sqlite table with the following schema and data:

CREATE TABLE Feeds (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    groups JSON    NOT NULL
                   DEFAULT ('[]') 
);

INSERT INTO Feeds(groups) VALUES ('["fav1", "fav2"]');
INSERT INTO Feeds(groups) VALUES ('["fav3", "fav4"]');
INSERT INTO Feeds(groups) VALUES ('["fav1"]');
INSERT INTO Feeds(groups) VALUES ('["fav1", "fav2", "fav5"]');

I want to find all the rows that have both the fav1 group and the fav2 group. I am able to query a single group via the following:

SELECT * FROM Feeds, json_each(groups) WHERE json_each.value IS "fav1"

But i am struggling to figure out how to query more than one group, the following doesnt seem to work:

SELECT * FROM Feeds, json_each(groups) WHERE json_each.value IS "fav1" AND json_each.value IS "fav2"

forpas · Accepted Answer · 2022-03-19 08:19:34Z

3

You can do it with aggregation:

SELECT f.* 
FROM Feeds f, json_each(groups) t 
WHERE t.value IN ('fav1', 'fav2')
GROUP BY f.id
HAVING COUNT(DISTINCT t.value) = 2;

You can change COUNT(DISTINCT t.value) to just COUNT(*) if there are no duplicates inside the json array.

See the demo.

answered Mar 19, 2022 at 8:19

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering multiple items in json array with sqlite json_each

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related