Command guide

intermediate json MySQL MariaDB SQL Server PostgreSQL SQLite

Count Elements In A JSON Array

Return the number of items stored in a JSON array 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

Count tags stored in a JSON array column

The first JSON array has two elements and the second has one. The array is not split into output rows; each post remains one row with a count.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  posts
VALUES
  (1, '["sql","json"]'),
  (2, '["database"]');

Validated query

SQL

SELECT
  id,
  JSON_LENGTH (tags) AS tag_count
FROM
  posts
ORDER BY
  id;

Expected result

id	tag_count
1	2
2	1

SQL Server

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  posts
VALUES
  (1, '["sql","json"]'),
  (2, '["database"]');

Validated query

SQL

SELECT
  id,
  (
    SELECT
      COUNT(*)
    FROM
      OPENJSON (tags)
  ) AS tag_count
FROM
  posts
ORDER BY
  id;

Expected result

id	tag_count
1	2
2	1

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, tags JSONB);

INSERT INTO
  posts
VALUES
  (1, '["sql","json"]'),
  (2, '["database"]');

Validated query

SQL

SELECT
  id,
  JSONB_ARRAY_LENGTH (tags) AS tag_count
FROM
  posts
ORDER BY
  id;

Expected result

id	tag_count
1	2
2	1

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE posts (id INT, tags TEXT);

INSERT INTO
  posts
VALUES
  (1, '["sql","json"]'),
  (2, '["database"]');

Validated query

SQL

SELECT
  id,
  json_array_length (tags) AS tag_count
FROM
  posts
ORDER BY
  id;

Expected result

id	tag_count
1	2
2	1

SQL Server counts rows returned by OPENJSON because it does not expose a direct JSON_ARRAY_LENGTH scalar function.

Useful when

Where this command helps.

finding rows with at least one JSON tag
auditing how many items are stored in a JSON array column

Explanation

What the command is doing.

JSON array length functions are useful when records store tags, selected options, or embedded child values as JSON. Each engine uses a different function name or JSON path convention, but the task is the same: count the elements at a JSON array path.

Count Elements In A JSON Array

Count tags stored in a JSON array column

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.