Command guide

advanced json MySQL MariaDB SQL Server PostgreSQL SQLite

Read a Value From a JSON Column

Extract a scalar value from a JSON column using the JSON function or operator each engine supports.

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

Docker-validated Not currently validation-green

Example 1

Read the theme preference stored in a JSON settings column

All three users have a theme key extracted as plain text. MySQL and MariaDB use JSON_EXTRACT to read the JSON path and JSON_UNQUOTE to turn the JSON string into a regular SQL string. PostgreSQL's ->> operator takes a plain key name without the $ prefix. SQL Server treats the JSON as text and uses JSON_VALUE with a JSONPath string. SQLite uses json_extract with the same JSONPath syntax as MySQL and MariaDB.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  users (id, name, settings)
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,
  JSON_UNQUOTE (JSON_EXTRACT (settings, '$.theme')) AS theme
FROM
  users
ORDER BY
  id;

Expected result

id	name	theme
1	Alice	dark
2	Bob	light
3	Carol	dark

SQL Server

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  users (id, name, settings)
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,
  JSON_VALUE (settings, '$.theme') AS theme
FROM
  users
ORDER BY
  id;

Expected result

id	name	theme
1	Alice	dark
2	Bob	light
3	Carol	dark

PostgreSQL

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  users (id, name, settings)
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,
  settings - > > 'theme' AS theme
FROM
  users
ORDER BY
  id;

Expected result

id	name	theme
1	Alice	dark
2	Bob	light
3	Carol	dark

SQLite

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  users (id, name, settings)
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,
  json_extract (settings, '$.theme') AS theme
FROM
  users
ORDER BY
  id;

Expected result

id	name	theme
1	Alice	dark
2	Bob	light
3	Carol	dark

MySQL and MariaDB use `JSON_UNQUOTE(JSON_EXTRACT(...))`; PostgreSQL uses `->>'key'`; SQL Server uses `JSON_VALUE(...)`; SQLite uses `json_extract(...)`. The column type and JSON access syntax differ, but the extracted text result is identical.

Useful when

Where this command helps.

reading a specific attribute out of a semi-structured settings or metadata column
filtering rows based on a value embedded inside a JSON document

Explanation

What the command is doing.

Each engine stores and queries JSON differently. MySQL and MariaDB can extract a scalar with JSON_UNQUOTE(JSON_EXTRACT(col, '$.key')). PostgreSQL uses JSONB and the ->> operator with a plain key name. SQL Server stores JSON as NVARCHAR and uses JSON_VALUE(col, '$.key'). SQLite uses json_extract(col, '$.key'). All of these forms return the selected JSON value as plain text for this example.

Read a Value From a JSON Column

Read the theme preference stored in a JSON settings column

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.