SQL JSON Functions: Query and Manipulate JSON Data (All Databases)

Overview

JSON support in SQL databases has matured rapidly. You can store, query, and manipulate JSON data natively — no application code needed.

MySQL

-- Store JSON:
CREATE TABLE products (
  id INT PRIMARY KEY,
  data JSON
);

-- Extract values:
SELECT JSON_EXTRACT(data, '$.name') AS name FROM products;
SELECT data->>'$.name' AS name FROM products;  -- unquoted
SELECT data->'$.tags[0]' AS first_tag FROM products;

-- Search in JSON:
SELECT * FROM products
WHERE JSON_EXTRACT(data, '$.price') > 100;

-- Create JSON:
SELECT JSON_OBJECT('name', name, 'price', price) FROM products;
SELECT JSON_ARRAY(1, 2, 3);  -- [1, 2, 3]

-- Modify JSON:
SELECT JSON_SET(data, '$.price', 99.99) FROM products;
SELECT JSON_INSERT(data, '$.new_field', 'value') FROM products;
SELECT JSON_REMOVE(data, '$.old_field') FROM products;

PostgreSQL

-- JSONB type (preferred over JSON):
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- Extract values:
SELECT data->>'name' AS name FROM products;     -- text
SELECT data->'address'->>'city' FROM products;   -- nested
SELECT data#>>'{address,city}' FROM products;    -- path

-- Search with operators:
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"active": true}';
SELECT * FROM products WHERE data ? 'premium';  -- has key

-- Index JSONB (GIN):
CREATE INDEX idx_products_data ON products USING GIN (data);

-- Aggregate to JSON:
SELECT json_agg(row_to_json(t)) FROM users t;
SELECT jsonb_build_object('total', COUNT(*)) FROM orders;

SQL Server

-- Store as NVARCHAR (no native JSON type):
CREATE TABLE products (
  id INT PRIMARY KEY,
  data NVARCHAR(MAX)
);

-- Extract values:
SELECT JSON_VALUE(data, '$.name') FROM products;   -- scalar
SELECT JSON_QUERY(data, '$.tags') FROM products;    -- object/array

-- Search:
SELECT * FROM products
WHERE JSON_VALUE(data, '$.price') > 100;

-- ISJSON validation:
SELECT * FROM products WHERE ISJSON(data) = 1;

-- OPENJSON (parse JSON to table):
SELECT *
FROM OPENJSON('{"name":"Widget","price":99}')
WITH (name VARCHAR(100), price DECIMAL(10,2));

-- FOR JSON (rows to JSON):
SELECT name, price FROM products FOR JSON PATH;

Skip the Syntax Lookup

Instead of memorizing JSON Functions syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions