JSON Functions And Operators

Verifies that the input is valid JSON and returns a minified version of the JSON string. If the input is not valid JSON, the function raises an error.

Example:

json(' { "name": "John", "age": 30 } ')

Output:

{"name":"John","age":30}

Use Case: Validate and minify JSON strings for storage or transmission.

Creates a JSON array from the provided values.

Example:

json_array(1, 'apple', true)

Output:

[1, "apple", true]

Use Case: Construct JSON arrays dynamically from SQL values.

Returns the number of elements in a JSON array. If a path is provided, it evaluates the array at that path.

Example:

json_array_length('[1, 2, 3]')

Output:

3

Use Case: Determine the size of a JSON array for iteration or validation.

Extracts values from a JSON document based on the specified path(s).

Example:

json_extract('{"name": "John", "age": 30}', '$.name')

Output:

"John"

Use Case: Retrieve specific values from nested JSON structures.

Inserts a value into a JSON document at the specified path if the path does not already exist.

Example:

json_insert('{"name": "John"}', '$.age', 30)

Output:

{"name":"John","age":30}

Use Case: Add new key-value pairs to JSON without overwriting existing data.

Creates a JSON object from the provided label-value pairs.

Example:

json_object('name', 'John', 'age', 30)

Output:

{"name":"John","age":30}

Use Case: Construct JSON objects dynamically from SQL data.

Merges two JSON objects. It applies the changes from json2 onto json1, modifying or adding new key-value pairs. If a key exists in both, the value from json2 replaces the one in json1.

Example 1: Updating a JSON Object

json_patch('{"name":"Alice","age":25}', '{"age":30,"city":"New York"}');

Output:

{"name":"Alice","age":30,"city":"New York"}

• The age is updated from 25 to 30.
• A new key “city” is added.

Example 2: Adding New Key-Value Pairs

json_patch('{"product":"Laptop","price":1000}', '{"discount":10}');

Output:

{"product":"Laptop","price":1000,"discount":10}

• The “discount” field is added to the original JSON.

Example 3: Overwriting Nested Objects

json_patch(
  '{"user":{"name":"John","age":40},"status":"active"}',
  '{"user":{"age":45}}'
);

Output:

{"user":{"age":45},"status":"active"}

• The original “user” object is overwritten entirely, keeping only "age": 45.

Example 4: JSON Array Handling

json_patch('{"items":[1,2,3]}', '{"items":[4,5]}');

Output:

{"items":[4,5]}

• Arrays are completely replaced, not merged.

Deletes one or more keys from a JSON object at the specified JSON path(s). If a specified key does not exist, the function returns the original JSON unchanged.

Example 1: Remove a Single Key from JSON

json_remove('{"name": "John", "age": 30}', '$.age');

Output:

{"name":"John"}

Use Case: Delete specific keys from JSON data.

Example 2: Remove Multiple Keys from JSON

json_remove('{"name":"Bob","email":"bob@email.com","phone":"123-456-7890"}', '$.email', '$.phone');

Output:

{"name":"Bob"}

• Both “email” and “phone” are removed.

Example 3: Removing a Nested Key

If JSON contains nested objects, we can remove specific keys inside them:

json_remove('{"user":{"name":"Charlie","age":40,"preferences":{"theme":"dark","notifications":true}}}', '$.user.preferences.notifications');

Output:

{"user":{"name":"Charlie","age":40,"preferences":{"theme":"dark"}}}

• "notifications" was removed from the “preferences” object.
• Other nested data remains intact.

Example 4: Attempting to Remove a Non-Existent Ke

json_remove('{"id":101,"status":"active"}', '$.email');

Output:

{"id":101,"status":"active"}

• Since "email" does not exist, the JSON is returned unchanged.

Replaces the value at the specified JSON path(s) only if the key already exists. If the specified path does not exist, the function returns the original JSON unchanged.

Example 1: Replacing a Single Value in JSON

Suppose we have a JSON object storing a product’s details:

json_replace('{"product":"Laptop","price":1200,"stock":50}', '$.price', 999);

Output:

{"product":"Laptop","price":999,"stock":50}

• The “price” was updated from 1200 to 999.
• The rest of the JSON remains unchanged.

Example 2: Replacing Multiple Values

json_replace('{"user":"Alice","age":30,"membership":"gold"}', '$.age', 35, '$.membership', 'platinum');

Output:

{"user":"Alice","age":35,"membership":"platinum"}

• Both “age” and “membership” were updated in a single operation.

Example 3: Attempting to Replace a Non-Existent Key

If the key does not exist, json_replace does not add it:

json_replace('{"id":101,"status":"active"}', '$.email', 'user@example.com');

Output:

{"id":101,"status":"active"}

• Since "email" did not exist, the JSON remains unchanged.

Example 4: Replacing a Nested Value

If JSON contains nested objects, we can replace values inside them:

json_replace('{"user":{"name":"Charlie","settings":{"theme":"light","notifications":true}}}', '$.user.settings.theme', 'dark');

Output:

{"user":{"name":"Charlie","settings":{"theme":"dark","notifications":true}}}

• The "theme" inside "settings" changed from "light" to "dark".

Sets or updates a value at the specified JSON path. If the key does not exist, it will create it.

Example 1: Updating an Existing Value in JSON

Suppose we have a JSON object storing a product’s details:

json_set('{"product":"Laptop","price":1200,"stock":50}', '$.price', 999);

Output:

{"product":"Laptop","price":999,"stock":50}

• The “price” was updated from 1200 to 999.

Example 2: Adding a New Key if It Does Not Exist

Unlike json_replace, json_set creates the key if it is missing:

json_set('{"id":101,"status":"active"}', '$.email', 'user@example.com');

Output:

{"id":101,"status":"active","email":"user@example.com"}

• The “email” field was added because it did not exist.

Example 3: Updating Multiple Values at Once

json_set('{"user":"Alice","age":30}', '$.age', 35, '$.membership', 'gold');

Output:

{"user":"Alice","age":35,"membership":"gold"}

• “age” was updated to 35, and
• “membership” was added since it did not exist.

Example 4: Modifying a Nested JSON Object

If JSON contains nested objects, we can update values inside them:

json_set('{"user":{"name":"Charlie","settings":{"theme":"light","notifications":true}}}', '$.user.settings.theme', 'dark');

Output:

{"user":{"name":"Charlie","settings":{"theme":"dark","notifications":true}}}

• The "theme" inside "settings" changed from "light" to "dark".

Example 5: Using json_set to Add a Key in a Nested Object

If the key does not exist, it will be added inside the correct level:

json_set('{"user":{"name":"Charlie"}}', '$.user.settings.theme', 'dark');

Output:

{"user":{"name":"Charlie","settings":{"theme":"dark"}}}

• The "settings" object was created with "theme": "dark" inside it.

returns the data type of the value stored at a given JSON path. If no path is provided, it checks the type of the entire JSON value.

Example 1: Checking the Type of a JSON Value

This checks the type of a full JSON object:

json_type('{"name":"Alice","age":30,"isMember":true}');

Output:

object

• The function recognizes that the input is a JSON object.

Example 2: Checking the Type of a Specific Key

Let’s check the type of individual keys in a JSON object:

json_type('{"name":"Alice","age":30,"isMember":true}', '$.name');

Output:

text

• The "name" field contains a text (string) value.

Similarly, checking the "age" field:

json_type('{"name":"Alice","age":30,"isMember":true}', '$.age');

Output:

integer

• The "age" field is an integer.

And checking "isMember":

json_type('{"name":"Alice","age":30,"isMember":true}', '$.isMember');

Output:

boolean

• The "isMember" field is a boolean.

Example 3: Checking the Type of a Nested Key

If the JSON contains nested objects, we can check their types:

json_type('{"user":{"id":101,"preferences":{"notifications":true}}}', '$.user.preferences.notifications');

Output:

boolean

• The "notifications" field is a boolean.

Example 4: Checking the Type of a JSON Array

If a field contains a JSON array, json_type will return "array":

json_type('{"ids":[101,102,103]}', '$.ids');

Output:

array

• The "ids" field is an array.

Checking an element inside the array:

json_type('{"ids":[101,102,103]}', '$.ids[0]');

Output:

integer

• The first element in the array (101) is an integer.

Example 5: Checking the Type of a Null Value

If a field is null, json_type will return "null":

json_type('{"name":null}', '$.name');

Output:

null

• "name" has a null value.

Example 6: Checking an Invalid Path

If the path does not exist, json_type returns NULL:

json_type('{"user":"Alice"}', '$.email');

Output:

NULL

• "email" does not exist, so the result is NULL.

checks whether a given JSON string is valid. It returns 1 (true) if the JSON is valid and 0 (false) if it is not.

Example 1: Valid JSON Object

Let’s check if a properly formatted JSON object is valid:

json_valid('{"name":"Alice","age":30}');

Output:

1

• The JSON structure is valid.

Example 2: Invalid JSON Object (Missing Quotes)

This example has a missing double quote around the key name, making it invalid:

json_valid('{name:"Alice","age":30}');

Output:

0

• The JSON structure is invalid because JSON keys must be enclosed in double quotes.

Example 3: Valid JSON Array

Checking a valid JSON array:

json_valid('[1, 2, 3, 4]');

Output:

1

• The JSON array is valid.

Example 4: Invalid JSON Array (Trailing Comma)

This JSON array contains a trailing comma, which is not allowed in JSON:

json_valid('[1, 2, 3,]');

Output:

0

• The JSON is invalid due to the trailing comma after 3.

Example 5: Checking a NULL Value

If NULL is passed, it returns 0, as NULL is not considered valid JSON:

json_valid(NULL);

Output:

0

• NULL is not valid JSON.

Example 6: Valid vs. Invalid Boolean & Numbers

Checking valid and invalid JSON values:

json_valid('true'), json_valid('123'), json_valid('03');

Output:

json_valid('true') | json_valid('123') | json_valid('03')
------------------|------------------|------------------
1                | 1                | 0

• true and 123 are valid JSON values.
• 03 is invalid because JSON does not allow leading zeros in numbers

takes a value and returns it as a JSON-encoded string with proper escaping and double quotes if necessary. It ensures that special characters inside the value are properly escaped.

Example 1: Quoting a String

json_quote('Hello World');

Output:

"Hello World"

• The string is enclosed in double quotes because JSON requires strings to be quoted.

Example 2: Handling Special Characters

json_quote('He said "Hello"');

Output:

"He said \"Hello\""

• The double quotes inside the string are escaped using \" so that it remains a valid JSON string.

Example 3: Quoting a NULL Value

json_quote(NULL);

Output:

NULL

• json_quote() returns NULL if the input is NULL, rather than "null".

Example 4: Quoting a JSON String with a Backslash

json_quote('C:\Users\Admin');

Output:

"C:\\Users\\Admin"

• The backslashes are escaped (\\) to ensure correct JSON formatting.

Example 5: Quoting a Number

json_quote(123);

Output:

"123"

• Numbers are converted to strings and enclosed in double quotes.

Example 6: Quoting a Boolean Value

json_quote('true');

Output:

"true"

• The boolean is treated as a string and quoted.

Example 7: Quoting a JSON Object

json_quote('{"name": "Alice"}');

Output:

"{\"name\": \"Alice\"}"

• The JSON object is treated as a string, and all internal quotes are escaped.