JSON in SQL: A Comprehensive Guide

Introduction

JSON (JavaScript Object Notation) is a popular data format for unstructured data. It allows storing composite data types, such as arrays and objects, in a single SQL value. Many modern applications use JSON to store and exchange data. As a result, SQL databases have incorporated JSON support to allow developers to work with structured and semi-structured data within the same database.

This guide will cover JSON operations in SQLite, PostgreSQL, MySQL, and SQL Server, focusing on querying JSON data.

SQLPage uses JSON both to pass data to the database (when a SQLPage variable contains an array), and to pass data to components (when a component has a JSON parameter). Thus, understanding how to work with JSON in SQL will allow you to fully leverage advanced SQLPage features.

JSON supports the following data types:

• Objects: A mapping between keys and values ({ "key": "value" }). Keys must be strings, and values can be of different types.
• Arrays: An ordered list of values enclosed in square brackets ([ "value1", "value2" ]). Values can be of different types.
• Strings: A sequence of characters enclosed in double quotes ("Hello, World!").
• Numbers: An integer or floating-point number (42, 3.14).
• Boolean: A true or false value (true, false).
• Null: A null value (null).

Sample Table

We'll use the following sample table for our examples:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    birthday DATE,
    group_name VARCHAR(50)
);

INSERT INTO users (id, name, birthday, group_name) VALUES
(1, 'Alice', '1990-01-15', 'Admin'),
(2, 'Bob', '1985-05-22', 'User'),
(3, 'Charlie', '1992-09-30', 'User');

SQLite

SQLite provides increasingly better JSON support since version 3.38.0. See the list of JSON functions in SQLite for more details.

Creating a JSON object

We can use the standard json_object() function to create a JSON object from columns in a table:

SELECT json_object('name', name, 'birthday', birthday) AS user_json
FROM users;

Creating a JSON array

SELECT json_array(name, birthday, group_name) AS user_array
FROM users;

Aggregating multiple values into a JSON array

SELECT json_group_array(name) AS names
FROM users;

Aggregating values into a JSON object

SELECT json_group_object(name, birthday) AS name_birthday_map
FROM users;

Iterating over a JSON array

SQLite provides the json_each() table-valued function to iterate over JSON arrays. This function returns one row for each element in the JSON array.

SELECT value FROM json_each('["Alice", "Bob", "Charlie"]');

The json_each() function returns a table with several columns. The most commonly used are:

• key: The array index (0-based) for elements of a JSON array
• value: The value of the current element
• type: The type of the current element (e.g., 'text', 'integer', 'real', 'true', 'false', 'null')

For more complex JSON structures, you can use the json_tree() function, which recursively walks through the entire JSON structure.

These iteration functions can be used to test whether a value is present in a JSON array. For instance, to create a multi-value select dropdown with pre-selected values, you can use the following query:

select json_group_array(json_object(
    'label', name
    'value', id,
    'selected', id in (select value from json_each_text($selected_ids))
)) as options
from users;

Combining two JSON objects

SQLite provides the json_patch() function to combine two JSON objects. This function takes two JSON objects as arguments and returns a new JSON object that is the result of merging the two input objects.

SELECT json_patch('{"name": "Alice"}', '{"birthday": "1990-01-15"}') AS user_json;

PostgreSQL

PostgreSQL has extensive support for JSON, including the jsonb type, which offers better performance and more functionality than the json type. See the list of JSON functions in PostgreSQL for more details.

Creating a JSON object

SELECT jsonb_build_object('name', name, 'birthday', birthday) AS user_json FROM users;

Creating a JSON array

SELECT jsonb_build_array(name, birthday, group_name) AS user_array FROM users;

Aggregating multiple values into a JSON array

SELECT jsonb_agg(name) AS names FROM users;

Aggregating values into a JSON object

SELECT jsonb_object_agg(name, birthday) AS name_birthday_map
FROM users;

Iterating over a JSON array

SELECT name FROM jsonb_array_elements_text('["Alice", "Bob", "Charlie"]'::jsonb) AS name;

You can use this function to test whether a value is present in a JSON array. For instance, to create a multi-value select dropdown with pre-selected values, you can use the following query:

SELECT jsonb_agg(jsonb_build_object(
    'label', name,
    'value', id,
    'selected', id in (SELECT value FROM jsonb_array_elements_text($selected_ids::jsonb))
)) AS options
FROM users;

Iterating over a JSON object

SELECT key, value
FROM jsonb_each_text('{"name": "Alice", "birthday": "1990-01-15"}'::jsonb);

Querying JSON data

PostgreSQL allows you to query JSON data using the -> and ->> operators:

SELECT name, user_data->>'age' AS age
FROM (
    SELECT name, jsonb_build_object('age', EXTRACT(YEAR FROM AGE(birthday))) AS user_data
    FROM users
) subquery
WHERE (user_data->>'age')::int > 30;

Combining two JSON objects

PostgreSQL provides the || operator to combine two JSON objects.

SELECT '{"name": "Alice"}'::jsonb || '{"birthday": "1990-01-15"}'::jsonb AS user_json;

MySQL / MariaDB

MySQL has good support for JSON operations starting from version 5.7. See the list of JSON functions in MySQL for more details.

Creating a JSON object

SELECT JSON_OBJECT('name', name, 'birthday', birthday) AS user_json
FROM users;

Creating a JSON array

SELECT JSON_ARRAY(name, birthday, group_name) AS user_array
FROM users;

Aggregating multiple values into a JSON array

SELECT JSON_ARRAYAGG(name) AS names
FROM users;

Aggregating values into a JSON object

SELECT JSON_OBJECTAGG(name, birthday) AS name_birthday_map
FROM users;

Iterating over a JSON array

MySQL provides the JSON_TABLE() function to iterate over JSON arrays. This powerful function allows you to convert JSON data into a relational table format, making it easy to work with JSON arrays.

Here's an example of how to use JSON_TABLE() to iterate over a JSON array:

SELECT jt.*
FROM JSON_TABLE(
  '["Alice", "Bob", "Charlie"]',
  '$[*]' COLUMNS(
    row_num FOR ORDINALITY,
    name VARCHAR(50) PATH '$'
  )
) AS jt;

In this example:

• The first argument to JSON_TABLE() is the JSON array.
• '$[*]' is the path expression that selects all elements of the array.
• COLUMNS clause defines the structure of the output table:
  • row_num FOR ORDINALITY creates a column that numbers the rows.
  • name VARCHAR(50) PATH '$' creates a column that contains the value of each array element.

You can also use JSON_TABLE() with more complex JSON structures:

SELECT jt.*
FROM JSON_TABLE(
  '[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}, {"id": 3, "name": "Charlie"}]',
  '$[*]' COLUMNS(
    row_num FOR ORDINALITY,
    id INT PATH '$.id',
    name VARCHAR(50) PATH '$.name'
  )
) AS jt;

This approach allows you to easily iterate over JSON arrays and access their elements in a tabular format, which can be very useful for further processing or joining with other tables in your database.

Iterating over a JSON object

MySQL provides the JSON_TABLE function to iterate over JSON objects:

SELECT jt.*
FROM JSON_TABLE(
  '{"name": "Alice", "birthday": "1990-01-15"}',
  '$.*' COLUMNS (
    value JSON PATH '$'
  )
) AS jt;

Querying JSON data

MySQL allows you to query JSON data using the -> and ->> operators:

SELECT name, user_data->'$.age' AS age
FROM (
    SELECT name, JSON_OBJECT('age', YEAR(CURDATE()) - YEAR(birthday)) AS user_data
    FROM users
) subquery
WHERE user_data->'$.age' > 30;

Microsoft SQL Server

SQL Server has support for JSON operations starting from SQL Server 2016. See the list of JSON functions in SQL Server for more details.

JSON in SQL: A Comprehensive Guide

[Previous sections remain unchanged]

Microsoft SQL Server

SQL Server has support for JSON operations starting from SQL Server 2016. It provides a comprehensive set of functions for working with JSON data. See the list of JSON functions in SQL Server for more details.

Creating a JSON object

Use the FOR JSON PATH clause to create a JSON object:

SELECT (SELECT name, birthday FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS user_json
FROM users;

Alternatively, you can use the JSON_OBJECT function:

SELECT JSON_OBJECT('name': name, 'birthday': birthday) AS user_json
FROM users;

Creating a JSON array

Use the FOR JSON PATH clause to create a JSON array:

SELECT (SELECT name, birthday, group_name FOR JSON PATH) AS user_array
FROM users;

You can also use the JSON_ARRAY function:

SELECT JSON_ARRAY(name, birthday, group_name) AS user_array
FROM users;

Aggregating multiple values into a JSON array

Use the FOR JSON PATH clause to aggregate values into a JSON array:

SELECT (SELECT name FROM users FOR JSON PATH) AS names;

Alternatively, use the JSON_ARRAYAGG function:

SELECT JSON_ARRAYAGG(name) AS names FROM users;

Aggregating values into a JSON object

SELECT JSON_OBJECTAGG(name: birthday) AS name_birthday_map FROM users;

Iterating over a JSON array

Use the OPENJSON function to iterate over JSON arrays:

SELECT value FROM OPENJSON('["Alice", "Bob", "Charlie"]');

Iterating over a JSON object

Use OPENJSON to iterate over JSON objects:

SELECT *
FROM OPENJSON('{"name": "Alice", "birthday": "1990-01-15"}')
WITH (
    name NVARCHAR(50) '$.name',
    birthday DATE '$.birthday'
);

Querying JSON data

Use the JSON_VALUE function to extract scalar values from JSON:

SELECT JSON_VALUE('{"age": 38}', '$.age') AS age

Additional JSON Functions

SQL Server provides several other useful JSON functions:

• ISJSON: Tests whether a string contains valid JSON.
• JSON_MODIFY: Updates the value of a property in a JSON string.
• JSON_PATH_EXISTS: Tests whether a specified SQL/JSON path exists in the input JSON string.
• JSON_QUERY: Extracts an object or an array from a JSON string.

Example using JSON_MODIFY:

SELECT JSON_MODIFY('{"name": "Alice", "age": 30}', '$.age', 31) AS updated_json;

This comprehensive guide covers the basics of working with JSON in SQLite, PostgreSQL, MySQL, and SQL Server. Each database has its own set of functions and syntax for JSON operations, but the general concepts remain similar across all platforms.