codejump
MediumPro challengeSQL

Filter on a Nested JSON Field

SQLDatabasesJSON

Table: users

idnameprofile
1Alice{"address":{"city":"Paris","zip":"75001"}}
2Bob{"address":{"city":"Lyon","zip":"69001"}}
3Carol{"address":{"city":"Paris","zip":"75002"}}

Write a query that returns the id and name of every user whose
profile.address.city is 'Paris'.

Expected output (columns: id, name), ordered by id:

1|Alice
3|Carol

Hint — json_extract accepts multi-level paths directly:

json_extract(profile, '$.address.city') reaches two levels deep in one

call. You can filter on the extracted value in a normal WHERE clause.

Sample tests

Test #1Two of three users match
Input: "CREATE TABLE users (id INTEGER, name TEXT, profile TEXT);\nINSERT INTO users VALUES\n (1, 'Alice', '{\"address\":{\"city\":\"Paris\",\"zip\":\"75001\"}}'),\n (2, 'Bob', '{\"address\":{\"city\":\"Lyon\",\"zip\":\"69001\"}}'),\n (3, 'Carol', '{\"address\":{\"city\":\"Paris\",\"zip\":\"75002\"}}');"
Output: "1|Alice\n3|Carol"
Test #2No match returns an empty result
Input: "CREATE TABLE users (id INTEGER, name TEXT, profile TEXT);\nINSERT INTO users VALUES\n (1, 'Solo', '{\"address\":{\"city\":\"NYC\",\"zip\":\"10001\"}}');"
Output: ""
← All challenges