Command guide

intermediate json MySQL MariaDB SQL Server PostgreSQL SQLite

Filter Rows by a Value Inside a JSON Column

Use each engine's JSON path syntax in a `WHERE` clause to keep only rows whose JSON column contains a matching value.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find users who have selected the dark theme in their settings

Alice and Carol both have {"theme": "dark"} in their settings. Bob has "light" and is excluded. The JSON path $.theme navigates to the top-level theme key in each document. For repeated queries on a JSON column, PostgreSQL supports a functional index on the extracted value (CREATE INDEX ON users ((settings->>'theme'))) to avoid a full table scan.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), settings JSON);

INSERT INTO
  users
VALUES
  (1, 'Alice', '{"theme": "dark", "lang": "en"}'),
  (2, 'Bob', '{"theme": "light", "lang": "fr"}'),
  (3, 'Carol', '{"theme": "dark", "lang": "de"}');

Validated query

SQL

SELECT
  id,
  name
FROM
  users
WHERE
  JSON_UNQUOTE (JSON_EXTRACT (settings, '$.theme')) = 'dark'
ORDER BY
  id;

Expected result

id	name
1	Alice
3	Carol

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), settings NVARCHAR (MAX));

INSERT INTO
  users
VALUES
  (1, 'Alice', '{"theme": "dark", "lang": "en"}'),
  (2, 'Bob', '{"theme": "light", "lang": "fr"}'),
  (3, 'Carol', '{"theme": "dark", "lang": "de"}');

Validated query

SQL

SELECT
  id,
  name
FROM
  users
WHERE
  JSON_VALUE (settings, '$.theme') = 'dark'
ORDER BY
  id;

Expected result

id	name
1	Alice
3	Carol

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), settings JSONB);

INSERT INTO
  users
VALUES
  (1, 'Alice', '{"theme": "dark", "lang": "en"}'),
  (2, 'Bob', '{"theme": "light", "lang": "fr"}'),
  (3, 'Carol', '{"theme": "dark", "lang": "de"}');

Validated query

SQL

SELECT
  id,
  name
FROM
  users
WHERE
  settings - > > 'theme' = 'dark'
ORDER BY
  id;

Expected result

id	name
1	Alice
3	Carol

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE users (id INT, name VARCHAR(50), settings TEXT);

INSERT INTO
  users
VALUES
  (1, 'Alice', '{"theme": "dark", "lang": "en"}'),
  (2, 'Bob', '{"theme": "light", "lang": "fr"}'),
  (3, 'Carol', '{"theme": "dark", "lang": "de"}');

Validated query

SQL

SELECT
  id,
  name
FROM
  users
WHERE
  json_extract (settings, '$.theme') = 'dark'
ORDER BY
  id;

Expected result

id	name
1	Alice
3	Carol

MySQL and MariaDB use `JSON_UNQUOTE(JSON_EXTRACT(col, '$.key'))`. PostgreSQL uses `col->>'key'`. SQL Server uses `JSON_VALUE(col, '$.key')`. SQLite uses `json_extract(col, '$.key')`. All extract the value as text for comparison.

Useful when

Where this command helps.

selecting only rows whose settings or metadata column contains a specific attribute value
filtering events stored as JSON blobs by a type or status field

Explanation

What the command is doing.

Filtering on a value embedded inside a JSON column uses the same path functions as reading that value — you just move the expression into a WHERE clause. MySQL and MariaDB use JSON_UNQUOTE(JSON_EXTRACT(col, '$.key')). PostgreSQL uses the ->> operator with a plain key name. SQL Server uses JSON_VALUE(col, '$.key') on an NVARCHAR column. SQLite uses json_extract(col, '$.key'). All forms return the selected value as a plain string that can be compared with = or LIKE.

Filter Rows by a Value Inside a JSON Column

Find users who have selected the dark theme in their settings

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.