SELECT json_extract('{"a": {"b": [10, 20, 30]}}', '$.a.b[1]');
-- 20

json(json_text)

SELECT json('  { "name": "Alice" ,  "age": 30 } ');
-- {"name":"Alice","age":30}

jsonb(json_text)

SELECT typeof(jsonb('{"a":1}'));
-- blob

json_array(value1, value2, ...)
jsonb_array(value1, value2, ...)

SELECT json_array(1, 'hello', NULL, 3.14);
-- [1,"hello",null,3.14]

SELECT json_array();
-- []

json_object(label1, value1, label2, value2, ...)
jsonb_object(label1, value1, label2, value2, ...)
json_object(*)

SELECT json_object('name', 'Alice', 'age', 30);
-- {"name":"Alice","age":30}

SELECT json_object('items', json_array(1, 2, 3));
-- {"items":[1,2,3]}

json_quote(value)

SELECT json_quote('hello');
-- "hello"

SELECT json_quote(42);
-- 42

SELECT json_quote(NULL);
-- null

json_valid(json_text)

SELECT json_valid('{"name":"Alice"}');
-- 1

SELECT json_valid('not json');
-- 0

SELECT json_valid(NULL);
-- 0

json_error_position(json_text)

SELECT json_error_position('{"a":1}');
-- 0

SELECT json_error_position('{"a":}');
-- 6

json_extract(json_text, path)
json_extract(json_text, path1, path2, ...)
jsonb_extract(json_text, path)

SELECT json_extract('{"name":"Alice","age":30}', '$.name');
-- Alice

SELECT json_extract('{"name":"Alice","age":30}', '$.age');
-- 30

-- Multiple paths return a JSON array
SELECT json_extract('{"a":1,"b":2,"c":3}', '$.a', '$.c');
-- [1,3]

json_text -> path

SELECT '{"name":"Alice"}' -> '$.name';
-- "Alice"

SELECT '{"items":[1,2,3]}' -> '$.items';
-- [1,2,3]

json_text ->> path

SELECT '{"name":"Alice"}' ->> '$.name';
-- Alice

SELECT '{"count":42}' ->> '$.count';
-- 42

json_type(json_text)
json_type(json_text, path)

SELECT json_type('{"a":1}');
-- object

SELECT json_type('[1, 2, 3]');
-- array

SELECT json_type('{"a": 1}', '$.a');
-- integer

SELECT json_type('{"a": "hello"}', '$.a');
-- text

json_insert(json_text, path1, value1, path2, value2, ...)
jsonb_insert(json_text, path1, value1, path2, value2, ...)

SELECT json_insert('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

-- Existing values are NOT overwritten
SELECT json_insert('{"a":1}', '$.a', 99);
-- {"a":1}

json_replace(json_text, path1, value1, path2, value2, ...)
jsonb_replace(json_text, path1, value1, path2, value2, ...)

SELECT json_replace('{"a":1,"b":2}', '$.a', 99);
-- {"a":99,"b":2}

-- Non-existent paths are ignored
SELECT json_replace('{"a":1}', '$.b', 2);
-- {"a":1}

json_set(json_text, path1, value1, path2, value2, ...)
jsonb_set(json_text, path1, value1, path2, value2, ...)

-- Replace existing
SELECT json_set('{"a":1}', '$.a', 99);
-- {"a":99}

-- Insert new
SELECT json_set('{"a":1}', '$.b', 2);
-- {"a":1,"b":2}

json_remove(json_text, path1, path2, ...)
jsonb_remove(json_text, path1, path2, ...)

SELECT json_remove('{"a":1,"b":2,"c":3}', '$.b');
-- {"a":1,"c":3}

SELECT json_remove('[1,2,3,4]', '$[1]');
-- [1,3,4]

json_patch(json_text, patch)
jsonb_patch(json_text, patch)

SELECT json_patch('{"a":1,"b":2}', '{"b":3,"c":4}');
-- {"a":1,"b":3,"c":4}

-- null in the patch removes a key
SELECT json_patch('{"a":1,"b":2}', '{"b":null}');
-- {"a":1}

json_pretty(json_text)

SELECT json_pretty('{"name":"Alice","scores":[90,85,92]}');
/*
{
    "name": "Alice",
    "scores": [
        90,
        85,
        92
    ]
}
*/

json_array_length(json_text)
json_array_length(json_text, path)

SELECT json_array_length('[1, 2, 3, 4]');
-- 4

SELECT json_array_length('{"items": [10, 20]}', '$.items');
-- 2

SELECT json_array_length('{"a": 1}');
-- NULL

json_group_array(value)
jsonb_group_array(value)

CREATE TABLE items (category TEXT, name TEXT);
INSERT INTO items VALUES ('fruit', 'apple'), ('fruit', 'banana'), ('veggie', 'carrot');

SELECT category, json_group_array(name) FROM items GROUP BY category;
-- fruit  | ["apple","banana"]
-- veggie | ["carrot"]

json_group_object(label, value)
jsonb_group_object(label, value)

CREATE TABLE settings (key TEXT, value TEXT);
INSERT INTO settings VALUES ('theme', 'dark'), ('lang', 'en');

SELECT json_group_object(key, value) FROM settings;
-- {"theme":"dark","lang":"en"}

SELECT * FROM json_each(json_text);
SELECT * FROM json_each(json_text, path);

SELECT key, value, type FROM json_each('[10, "hello", null]');
-- 0 | 10    | integer
-- 1 | hello | text
-- 2 | null  | null

SELECT key, value FROM json_each('{"a":1, "b":2}');
-- a | 1
-- b | 2

-- With a path
SELECT key, value FROM json_each('{"data": [1, 2, 3]}', '$.data');
-- 0 | 1
-- 1 | 2
-- 2 | 3

SELECT * FROM json_tree(json_text);
SELECT * FROM json_tree(json_text, path);

SELECT key, value, type, path FROM json_tree('{"a": [1, 2]}');
-- NULL | {"a":[1,2]} | object  | $
-- a    | [1,2]       | array   | $
-- 0    | 1           | integer | $.a
-- 1    | 2           | integer | $.a

CREATE TABLE events (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO events VALUES (1, '{"type":"click","x":100,"y":200}');
INSERT INTO events VALUES (2, '{"type":"scroll","offset":500}');

-- Extract specific fields
SELECT id, data ->> '$.type' AS event_type FROM events;
-- 1 | click
-- 2 | scroll

-- Filter by JSON value
SELECT * FROM events WHERE data ->> '$.type' = 'click';

UPDATE events
SET data = json_set(data, '$.timestamp', '2025-01-15T10:30:00Z')
WHERE id = 1;

SELECT data FROM events WHERE id = 1;
-- {"type":"click","x":100,"y":200,"timestamp":"2025-01-15T10:30:00Z"}

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users VALUES (1, 'Alice', '[email protected]');
INSERT INTO users VALUES (2, 'Bob', '[email protected]');

SELECT json_object('users', json_group_array(
    json_object('id', id, 'name', name, 'email', email)
)) FROM users;
-- {"users":[{"id":1,"name":"Alice","email":"[email protected]"},{"id":2,"name":"Bob","email":"[email protected]"}]}

CREATE TABLE orders (id INTEGER PRIMARY KEY, items TEXT);
INSERT INTO orders VALUES (1, '["widget","gadget","gizmo"]');
INSERT INTO orders VALUES (2, '["sprocket"]');

-- Expand each order's items into individual rows
SELECT orders.id, each.value AS item
FROM orders, json_each(orders.items) AS each;
-- 1 | widget
-- 1 | gadget
-- 1 | gizmo
-- 2 | sprocket