Command guide

advanced json MySQL MariaDB SQL Server PostgreSQL SQLite

Filter JSON Arrays Containing A Value

Return rows where a JSON array contains a specific scalar value.

Created Apr 30, 2026 Last updated Apr 30, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Find posts whose tags array contains sql

Posts 1 and 3 include sql in the tags array. Post 2 contains only json, so it is excluded.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title VARCHAR(50), tags VARCHAR(200));

INSERT INTO
  posts
VALUES
  (1, 'Intro', '["sql","tutorial"]'),
  (2, 'API', '["json"]'),
  (3, 'Joins', '["sql","joins"]');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
WHERE
  JSON_CONTAINS (tags, '"sql"')
ORDER BY
  id;

Expected result

id	title
1	Intro
3	Joins

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title VARCHAR(50), tags NVARCHAR (200));

INSERT INTO
  posts
VALUES
  (1, 'Intro', '["sql","tutorial"]'),
  (2, 'API', '["json"]'),
  (3, 'Joins', '["sql","joins"]');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
WHERE
  EXISTS (
    SELECT
      1
    FROM
      OPENJSON (tags)
    WHERE
      VALUE = 'sql'
  )
ORDER BY
  id;

Expected result

id	title
1	Intro
3	Joins

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title VARCHAR(50), tags JSONB);

INSERT INTO
  posts
VALUES
  (1, 'Intro', '["sql","tutorial"]'),
  (2, 'API', '["json"]'),
  (3, 'Joins', '["sql","joins"]');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
WHERE
  tags ? 'sql'
ORDER BY
  id;

Expected result

id	title
1	Intro
3	Joins

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, title TEXT, tags TEXT);

INSERT INTO
  posts
VALUES
  (1, 'Intro', '["sql","tutorial"]'),
  (2, 'API', '["json"]'),
  (3, 'Joins', '["sql","joins"]');

Validated query

SQL

SELECT
  id,
  title
FROM
  posts
WHERE
  EXISTS (
    SELECT
      1
    FROM
      json_each (tags)
    WHERE
      VALUE = 'sql'
  )
ORDER BY
  id;

Expected result

id	title
1	Intro
3	Joins

The JSON predicate differs by engine, but all return posts whose array contains the string sql.

Useful when

Where this command helps.

finding posts tagged with a specific label
filtering records that contain a selected option in JSON data

Explanation

What the command is doing.

JSON containment queries check whether an array stored in a JSON column includes a target value. MySQL and MariaDB provide JSON_CONTAINS; PostgreSQL can use the ? operator on JSONB arrays of strings; SQL Server and SQLite expand the array with JSON table functions and test with EXISTS.

Filter JSON Arrays Containing A Value

Find posts whose tags array contains sql

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.