Subqueries with SQLite and JSON1

Question

I have just discovered JSON1 for SQLite and find that it would be the perfect answer to many of the issues I need to deal with in my Android app (I am using SQLCipher which offers a version of SQLite with JSON1 support). the SQLite documentation for JSON1 is very good but does not cover issues such as running subqueries - which in any case is somewhat beyond my SQL abilities.

Here is my test case

I begin by creating the table

CREATE TABLE users(name TEXT,phone TEXT)

to which I then add two rows

INSERT INTO users (name,phone) VALUES('Joe Bloggs','{"mobile":
"123456789","land":"987654321"}');

INSERT INTO users (name,phone) VALUES('Jane Bloggs','{"mobile":
"234567890","land":"098765432"}');

which I can then query to return, say, just the mobile number thus

 SELECT json_extract(users.phone, '$.mobile') FROM users;

which would return the result ["123456789","234567890"]

But suppose I want to get the landline number and name of the user with the mobile number 234567890. How would I go about writing a sub query with the available json_* primitive functions in order to simply return the result ["Jane Bloggs","234567890"]?

Answer 1

I was considering closing this question but felt that leaving it and the answer would be more useful to others. The SQLite JSON1 documentation does in fact explain how to do such things though the explanations are not immediaately apparent. The SQL to issue in this instance would be

SELECT users.name FROM users,json_each(users.phone,'$.cell') WHERE json_each.value = '234567890'