0

I have a table with a column that holds a JSON array, and I want to count the array members that match a certain criteria. When I run the query I always get a count of zero, so I assume that I have written the query incorrectly. (Of course it might be nicer if the data were broken out into actual tables instead of packed into JSON, but for for now let’s run with it as–is.)

Here is a minimal way to set up a database to reproduce the problem:

.mode line

create table if not exists stuff (id int, rts text);
delete from stuff;
insert into stuff values (42, '[{"id":"42","created_at":1662066000,"author":{"id":"42","community_id":[12,13]}},{"id":"43","created_at":1662066001,"author":{"id":"43","community_id":[13]}}]');

And here is my query to count the number of objects that have a 12 in their community_id:

SELECT id,
       (SELECT count(*)
        FROM json_each(rts)
        WHERE EXISTS (SELECT true
                      FROM json_each(json_extract(value, '$.author.community_id'))
                      WHERE value = 12)) as count
FROM stuff;

If you run it (in the sqlite3 Fiddle, for example), you’ll find that it returns a count of zero:

   id = 42
count = 0

A simpler query, which does not test the members of the inner community_id array but just checks the created_at timestamp does work as expected:

SELECT id,
       (SELECT count(*)
        FROM json_each(rts)
        WHERE json_extract(value, '$.created_at') <= 1662066000) as count
FROM stuff;

It feels to me as if the value used in the from clause of the inner–most select isn’t the community_id array, but is something else instead. Am I doing something wrong here, or should this query work as I expect it to?

1 Answer 1

2

Since value is a field of a virtual table generated by both json_each() table-valued functions (i.e. outer and inner), you must specify which one you are referring to:

SELECT id,
       (SELECT count(*)
        FROM json_each(rts) AS j1
        WHERE EXISTS (SELECT true
                      FROM json_each(json_extract(j1.value, '$.author.community_id')) as j2
                      WHERE j2.value = 12)) as count
FROM stuff;
3
  • When I woke up this morning, the phrase “it’s something else instead” was going through my head. Sure enough, adding an alias for the exact table I wanted it to query fixed the problem. Thank you! Do you know if there is a specification that covers this detail?
    – db48x
    Nov 22, 2022 at 17:11
  • @db48x here is doc for json_each(). If you refer to a bare column name in a subquery, it will first try to resolve it using a table in that subquery and if none of those tables have such a column, it will next look at the nearest "enclosing" query... and so on up to the outermost query. So you need to specify j1.value in the inner subquery, while j2.value could be written as value
    – Andrea B.
    Nov 23, 2022 at 8:28
  • Yes, I’ve read the documentation for json_each a few times already; it’s the resolution of names that I clearly don’t understand as well as I had thought. I had always thought that the FROM clause used names defined in the enclosing query, since its job was to define the table(s) used by the other clauses of the query. The documentation for SELECT doesn’t say anything about it, for example.
    – db48x
    Nov 23, 2022 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.