Querying JSON values and paths

Querying JSON values comes down to two jobs: reading one value out of a document, and keeping the rows whose documents hold the value you want. PostgreSQL, MySQL and SQLite use the same two arrows for the first job. -> returns the part you name as JSON, quotes and all, and ->> returns it as a plain SQL value that you can compare, add up or print. You name the part with a path: $ is the whole document, .city steps into the key city of an object, and [0] picks the first element of an array, so $.address.city reads the city inside the address. Querying JSON values in a WHERE clause is the second job: put the ->> form in the condition and compare it like a column. This lesson covers both jobs, each engine’s paths, and a number comparison that quietly returns the wrong rows.

Press Run on any example to try it in your browser on SQLite 3.49.1, the engine that sql.js 1.14.2 contains. This page runs SQLite only, so the PostgreSQL 18 and MySQL 8.4 forms it shows follow those engines’ manuals.

Two arrows: JSON out, or a plain value out

-- Customer profiles kept as JSON text: -> returns JSON, ->> returns a plain SQL value.
CREATE TABLE customers (id INTEGER PRIMARY KEY, profile TEXT);
INSERT INTO customers VALUES
  (1, '{"name": "Asha Kulkarni", "address": {"city": "Pune", "pin": "411001"},
        "phones": ["98200 11111", "98200 22222"]}'),
  (2, '{"name": "Ravi Menon", "address": {"city": "Kochi"}, "phones": []}');

SELECT id,
       profile -> '$.address.city'          AS city_json,
       profile ->> '$.address.city'         AS city,
       typeof(profile ->> '$.address.city') AS sql_type
FROM customers;

-- The same city through two arrows, the first and last phone, and a key that is not there.
SELECT id,
       profile -> 'address' ->> 'city' AS chained,
       profile ->> '$.phones[0]'       AS first_phone,
       profile ->> '$.phones[#-1]'     AS last_phone,
       profile ->> '$.address.state'   AS state
FROM customers;

┌────┬───────────┬───────┬──────────┐
│ id │ city_json │ city  │ sql_type │
├────┼───────────┼───────┼──────────┤
│ 1  │ "Pune"    │ Pune  │ text     │
│ 2  │ "Kochi"   │ Kochi │ text     │
└────┴───────────┴───────┴──────────┘
┌────┬─────────┬─────────────┬─────────────┬───────┐
│ id │ chained │ first_phone │ last_phone  │ state │
├────┼─────────┼─────────────┼─────────────┼───────┤
│ 1  │ Pune    │ 98200 11111 │ 98200 22222 │       │
│ 2  │ Kochi   │             │             │       │
└────┴─────────┴─────────────┴─────────────┴───────┘

In the first result, city_json is "Pune" with its double quotes: -> returned a piece of JSON text. city is Pune without quotes, and typeof() says it is SQL text. SQLite’s ->> turns each JSON type into its SQL counterpart: a string into TEXT, a number into INTEGER or REAL, true and false into 1 and 0, and JSON’s null into SQL NULL. Objects and arrays come back as JSON text from both arrows. Use -> when the value goes on into another JSON function, and ->> for everything you compare, sort or show.

The second query writes a path in pieces: profile -> 'address' ->> 'city' takes the address as JSON, then reads its city. On the right of an arrow, SQLite accepts a full path such as '$.address.city', a bare key such as 'address', or a whole number for an array position, where a negative number counts from the end. $.phones[#-1] is the last phone. A path that leads nowhere, such as $.address.state or the first phone of an empty list, returns NULL rather than an error.

Paths in the three engines

Paths look alike in the three engines, but small differences break a query copied from one to another. With the profile in a column doc:

• The city, as JSON: doc -> 'address' -> 'city' in PostgreSQL; doc -> '$.address.city' in MySQL and SQLite.
• The city, as a plain value: doc #>> '{address,city}' in PostgreSQL; doc ->> '$.address.city' in MySQL and SQLite.
• The first phone: doc -> 'phones' ->> 0 in PostgreSQL; doc ->> '$.phones[0]' in MySQL and SQLite.
• The last phone: doc -> 'phones' ->> -1 in PostgreSQL, doc ->> '$.phones[last]' in MySQL and doc ->> '$.phones[#-1]' in SQLite.
• What ->> returns: always text in PostgreSQL, text without JSON’s quotes in MySQL, and TEXT, INTEGER, REAL or NULL in SQLite.
• What may stand left of an arrow: any json or jsonb value in PostgreSQL, only a column name in MySQL, and any JSON text or JSONB in SQLite.

PostgreSQL’s arrows take one key or one position at a time, while #> and #>> take a whole path as a text array. In MySQL, -> and ->> are shorthand for JSON_EXTRACT() and JSON_UNQUOTE(JSON_EXTRACT()), so '{"a": 1}' ->> '$.a' is a syntax error there, while JSON_EXTRACT() accepts any expression.

Filtering rows by what their JSON holds

-- App events whose properties are one JSON object per event.
CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, props TEXT);
INSERT INTO events VALUES
  (1, 'app_open', '{"platform": "android", "app_version": "5.2.0"}'),
  (2, 'app_open', '{"platform": "ios", "app_version": "5.2.1"}'),
  (3, 'payment',  '{"platform": "android", "method": "UPI", "amount": 1250}'),
  (4, 'payment',  '{"platform": "web", "method": "card", "amount": 499}'),
  (5, 'app_open', '{"app_version": "5.1.9"}'),
  (6, 'payment',  '{"platform": "Android", "method": "UPI", "amount": 2100}');

SELECT id, name
FROM events
WHERE props ->> 'platform' = 'android';

SELECT props ->> 'platform' AS platform, count(*) AS events
FROM events
GROUP BY platform
ORDER BY events DESC, platform;

┌────┬──────────┐
│ id │   name   │
├────┼──────────┤
│ 1  │ app_open │
│ 3  │ payment  │
└────┴──────────┘
┌──────────┬────────┐
│ platform │ events │
├──────────┼────────┤
│ android  │ 2      │
│          │ 1      │
│ Android  │ 1      │
│ ios      │ 1      │
│ web      │ 1      │
└──────────┴────────┘

The filter is an ordinary comparison with the extracted text. Event 6 is missing because Android with a capital A is a different string: a filter on JSON is as exact as a filter on a column. The second query groups by the same expression. Event 5 has no platform key, so its platform is NULL and it forms a group of its own.

Comparing numbers: the arrow decides

-- Four payments: two apps send the amount as a JSON number, one as a JSON string.
CREATE TABLE payments (id INTEGER PRIMARY KEY, props TEXT);
INSERT INTO payments VALUES
  (1, '{"method": "UPI", "amount": 250}'),
  (2, '{"method": "UPI", "amount": "250"}'),
  (3, '{"method": "UPI", "amount": 1500}'),
  (4, '{"method": "card", "amount": "1500"}');

-- Three ways to write "amount > 1000": 1 means the row passes the filter.
SELECT id,
       props ->> 'amount'                      AS amount,
       typeof(props ->> 'amount')              AS sql_type,
       props -> 'amount' > 1000                AS with_arrow,
       props ->> 'amount' > 1000               AS with_double_arrow,
       CAST(props ->> 'amount' AS REAL) > 1000 AS cast_first
FROM payments;

┌────┬────────┬──────────┬────────────┬───────────────────┬────────────┐
│ id │ amount │ sql_type │ with_arrow │ with_double_arrow │ cast_first │
├────┼────────┼──────────┼────────────┼───────────────────┼────────────┤
│ 1  │ 250    │ integer  │ 1          │ 0                 │ 0          │
│ 2  │ 250    │ text     │ 1          │ 1                 │ 0          │
│ 3  │ 1500   │ integer  │ 1          │ 1                 │ 1          │
│ 4  │ 1500   │ text     │ 1          │ 1                 │ 1          │
└────┴────────┴──────────┴────────────┴───────────────────┴────────────┘

Only cast_first gets every row right: 1500 and “1500” pass, 250 and “250” do not. -> returns JSON text, and when SQLite compares values of different storage classes with no type to convert them to, every number ranks below every text value, so all four rows pass. ->> fixes the JSON numbers, but the amount sent as the string “250” still arrives as TEXT and still passes. Casting first compares numbers with numbers. PostgreSQL needs the cast too, because its ->> always returns text: (props ->> 'amount')::numeric > 1000.

Beyond the arrows

The arrows read one value at a time. Each engine also has tools for questions about whole documents:

• json_type() in SQLite tells a key that holds JSON’s null from a key that is missing.
• Containment, @>, asks PostgreSQL whether a document includes a given piece of JSON, such as {"vip": true}.
• The key test ? asks PostgreSQL whether a string is a top-level key or array element.
• JSON path filters such as $.items[*] ? (@.qty > 2) keep only the array elements that pass a test.
• MEMBER OF and JSON_CONTAINS() ask MySQL whether an array or a document holds a value.
• JSON_VALUE(), JSON_QUERY() and JSON_EXISTS(), from the SQL standard, read a value, read a piece of JSON or test a path; PostgreSQL has all three from version 17, MySQL only the first.
• json_each() turns an array into rows in SQLite, so that EXISTS can ask about any element.

Your turn

-- Return the id of every UPI payment over 1000 rupees.
SELECT id
FROM events
WHERE name = 'payment';

# Sample tests: each one builds a small events table, runs your query on it and compares the rows it returns
# (in any order).
tests:
  - name: keeps UPI payments over 1000 and nothing else
    setup: |
      CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, props TEXT);
      INSERT INTO events VALUES
        (1, 'payment', '{"payment_method": "UPI", "amount": 1250}'),
        (2, 'payment', '{"payment_method": "UPI", "amount": 999}'),
        (3, 'payment', '{"payment_method": "card", "amount": 5000}'),
        (4, 'payment', '{"payment_method": "UPI", "amount": 1000}'),
        (5, 'payment', '{"payment_method": "UPI", "amount": 1000.5}'),
        (6, 'app_open', '{"platform": "android"}');
    expected:
      columns: [id]
      rows:
        - [1]
        - [5]
  - name: compares amounts sent as JSON strings as numbers
    setup: |
      CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, props TEXT);
      INSERT INTO events VALUES
        (1, 'payment', '{"payment_method": "UPI", "amount": "1250"}'),
        (2, 'payment', '{"payment_method": "UPI", "amount": "250"}'),
        (3, 'payment', '{"payment_method": "UPI", "amount": 250}'),
        (4, 'refund', '{"payment_method": "UPI", "amount": 5000}');
    expected:
      columns: [id]
      rows:
        - [1]
  - name: leaves out payments without an amount or a method
    setup: |
      CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT, props TEXT);
      INSERT INTO events VALUES
        (1, 'payment', '{"payment_method": "UPI"}'),
        (2, 'payment', '{"payment_method": "UPI", "amount": null}'),
        (3, 'payment', '{"amount": 4000}'),
        (4, 'payment', '{"payment_method": "UPI", "amount": 30000, "upi_app": "BHIM"}');
    expected:
      columns: [id]
      rows:
        - [4]

Filters that misfire in each engine, reproduced Pro: opens with a Pro, Premium or Ultimate pass

Searching arrays and filtering with JSON paths, engine by engine Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• -> returns JSON text and ->> a plain SQL value: compare, sort and show the ->> form.
• Paths start with $ in MySQL and SQLite; PostgreSQL’s arrows take one key or position at a time, and #>> takes a whole path.
• A missing key, or a path that leads nowhere, returns NULL rather than an error.
• Cast extracted numbers before comparing them: SQLite ranks text above numbers, and PostgreSQL’s ->> always returns text.
• MySQL’s arrows work only on a column; use JSON_EXTRACT() on any other expression.