<< back SqlJson useful links

Usually I'm handle XML inside SQL, see for example

• XML (2009) SQL-Client_for_remote_XML-WebService - client to meteonova.ru
• XML (2009) How to parse XML SOAP inside MS SQL

But I never so far parse JSON inside SQL. And currently I have other project with parsing JSON inside SQL. Therefore I decide create this page where I have collected useful links about SQL JSON .

• https://www.sqlshack.com/how-to-parse-json-in-sql-server/



SELECT [value] ,[type],[key]
FROM OPENROWSET (BULK 'C:\sample-json-file.json', SINGLE_CLOB) as JsonFile
CROSS APPLY OPENJSON(BulkColumn)



• https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15



Extract values from JSON text and use them in queries

    FOR Clause (Transact-SQL) (FOR JSON) - Convert SQL Server data to JSON or export JSON, Return data from a SQL Server table formatted as JSON
    OPENJSON (Transact-SQL) - Convert JSON collections to a rowset, Import JSON data into SQL Server tables
    JSON Functions (Transact-SQL)
        ISJSON (Transact-SQL)
        JSON_VALUE (Transact-SQL)
        JSON_QUERY (Transact-SQL)
        JSON_MODIFY (Transact-SQL) - Change JSON values

Analyze JSON data with SQL queries OPENJSON/JSON_VALUE
Store JSON documents in SQL Server or SQL Database
Index JSON data
Optimize JSON processing with in-memory OLTP



• https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-data-sql-server?view=sql-server-ver15



XML Data Type and Columns (SQL Server)
XML Indexes (SQL Server)
XML Schema Collections (SQL Server)
FOR XML (SQL Server)
OPENXML (Transact-SQL)



• https://database.guide/json-functions-operators-in-sqlite-full-list/



Scalar Functions
Function	Description
json()	Returns a minified version of its (JSON string) argument as actual JSON. Basically, it converts raw text that looks like JSON into actual JSON.
json_array()	Returns a well-formed JSON array based on its arguments.
json_array_length()	Returns the number of elements in a given JSON array.
json_extract()	Extracts and returns one or more values from well-formed JSON.
json_insert()	Inserts a new value into a JSON document.
json_object()	Returns a well-formed JSON object based on its arguments.
json_patch()	Adds, modifies, and/or deletes elements of a JSON Object (it runs the RFC-7396 MergePatch algorithm to apply a given patch against the given JSON input).
json_remove()	Removes one or more elements from a JSON object or array.
json_replace()	Replaces an existing value in a JSON document with another value.
json_set()	Inserts into, or replaces, a value in a JSON document.
json_type()	Returns the type of the outermost element of the given JSON.
json_valid()	Checks whether or not its argument is well-formed JSON.
json_quote()	Converts a number or string into its corresponding JSON representation.
Aggregate Functions
Function	Description
json_group_array()	Returns a JSON array comprised of all values in the aggregation.
json_group_object()	Returns a JSON object comprised of all name/value pairs in the aggregation.
Table-Valued Functions
Function	Description
json_each()	Walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member. It only walks the immediate children of the top-level array or object, or just the top-level element itself if the top-level element is a primitive value.
json_tree()	Walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member. It recursively walks through the JSON substructure starting with the top-level element.
JSON Operators
SQLite also includes the following operators for working with JSON:
Operator	Description
->	Extracts a subcomponent from a JSON document and returns a JSON representation of that subcomponent.
->>	Extracts a subcomponent from a JSON document and returns an SQL representation of that subcomponent.



• https://stackoverflow.com/questions/47814217/create-nested-json-arrays-using-for-json-path



variables @Pets, @Persons
fields pr.person_id, person.pet, pt.pet_id, person.pet

SELECT pr.person_id AS [person.id], pr.person_name AS [person.name],
    (
        SELECT pt.pet_id AS id, pt.pet_name AS name 
        FROM @Pets pt WHERE pt.pet_owner=pr.person_id 
        FOR JSON PATH
    ) AS [person.pet]
FROM @Persons pr 
FOR JSON PATH, ROOT('pet owners')



• https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/json-path-expressions.html



A SQL/JSON basic path expression (also called just a path expression here) is an absolute simple path expression, followed by an optional filter expression.
The optional filter expression can be present only when the path expression is used in SQL condition json_exists. No steps can follow the filter expression. (This is not allowed, for example: $.a?(@.b == 2).c.)
An absolute simple path expression begins with a dollar sign ($), which represents the path-expression context item, that is, the JSON data to be matched. That data is the result of evaluating a SQL expression that is passed as argument to the SQL/JSON function.
The dollar sign is followed by zero or more path steps. Each step can be an object step or an array step, depending on whether the context item represents a JSON object or a JSON array. The last step of a simple path expression can be a single, optional function step.
An object step is a period (.), sometimes read as "dot", followed by an object field name (object property name) or an asterisk (*) wildcard, which stands for (the values of) all fields. A field name can be empty, in which case it must be written as "". A nonempty field name must start with an uppercase or lowercase letter A to Z and contain only such letters or decimal digits (0-9), or else it must be enclosed in double quotation marks ("). An object step returns the value of the field that is specified. If a wildcard is used for the field then the step returns the values of all fields, in no special order.
An array step is a left bracket ([) followed by either an asterisk (*) wildcard, which stands for all array elements, or one or more specific array indexes or range specifications separated by commas, followed by a right bracket (]). In a path expression, array indexing is zero-based (0, 1, 2,...), as in the JavaScript convention for arrays. A range specification has the form N to M, where N and M are array indexes and N is strictly less than M. (An error is raised at query compilation time if N is not less than M.) An error is raised if you use both an asterisk and either an array index or range specification.
When indexes or range specifications are used, the array elements they collectively specify must be specified in ascending order, without repetitions, or else a compile-time error is raised. For example, an error is raised for each of [3, 1 to 4], [4, 2], [2, 3 to 3], and [2, 3, 3]. Errors are raised on the first two because the order is not ascending, Errors are raised on the last two because of the repetition of element number 3 (the fourth element, because of zero-based indexing).
Similarly, the elements in the array value that results from matching are in ascending order, with no repetitions. If an asterisk is used in the path expression then all of the array elements are returned, in array order.
A single function step is optional. If present, it is the last step of the path expression. It is a dot (.), followed by a SQL/JSON item method. It is followed by a left parenthesis (() and then a right parenthesis ()). The parentheses can have whitespace between them (such whitespace is insignificant). The function is applied to the data that is targeted by the rest of the same path expression, which precedes it. It is used to transform that data. The function or condition that is passed the path expression uses the transformed data in place of the targeted data.
Note:
If an item method is applied to an array, it is in effect applied to each of the array elements. For example, $.a.fun() applies item-method fun() to each element of array a, to convert it. The resulting array of converted values is then used for matching, in place of a.
If an item-method conversion fails for any reason, such as its argument being of the wrong type, then the path cannot be matched (it refers to no data), and no error is raised. In particular, this means that such an error is not handled by an error clause in the SQL/JSON function or condition to which the path expression is passed.

The available item methods are the following.
abs(): The absolute value of the targeted JSON number. Corresponds to the use of SQL function ABS.
ceiling(): The targeted JSON number, rounded up to the nearest integer. Corresponds to the use of SQL function CEIL.
date(): The SQL DATE value that corresponds to the targeted JSON string. The string data must be in one of the ISO date formats.
double(): The SQL BINARY_DOUBLE numeric value that corresponds to the targeted JSON string or number.
floor(): The targeted JSON number, rounded down to the nearest integer. Corresponds to the use of SQL function FLOOR.
length(): The number of characters in the targeted JSON string, as a SQL NUMBER.
lower(): The lowercase string that corresponds to the characters in the targeted JSON string.
number(): The SQL NUMBER value that corresponds to the targeted JSON string or number.
string(): A string representation of the targeted JSON value. The representation is the same as that used for the RETURNING clause of a SQL/JSON function with return type VARCHAR2. (A Boolean value is represented by the string "true" or "false"; a null value is represented by the string "null"; and a number is represented in a canonical form.) Any error that occurs during serialization to the string representation is ignored.
timestamp(): The SQL TIMESTAMP value that corresponds to the targeted JSON string. The string data must be in one of the ISO date formats.
upper(): The uppercase string that corresponds to the characters in the targeted JSON string.
Item methods date(), length(), lower(), number(), string(), timestamp(), and upper() are Oracle extensions to the SQL/JSON standard. The other item methods are part of the standard.
A filter expression (filter, for short) is a question mark (?) followed by a filter condition enclosed in parentheses (()). A filter is satisfied if its condition is satisfied, that is, returns true.
A filter condition applies a predicate (Boolean function) to its arguments and is one of the following, where each of cond, cond1, and cond2 stands for a filter condition.
( cond ): Parentheses are used for grouping, separating filter condition cond as a unit from other filter conditions that may precede or follow it.
cond1 && cond2: The conjunction (and) of cond1 and cond2, requiring that both be satisfied.
cond1 || cond2: The inclusive disjunction (or) of cond1 and cond2, requiring that cond1, cond2, or both, be satisfied.
! ( cond ): The negation of cond, meaning that cond must not be satisfied.
exists (, followed by a relative simple path expression, followed by ): The targeted data exists.
A comparison, which is one of the following:
A relative simple path expression, followed by a comparison predicate, followed by either a JSON scalar value or a SQL/JSON variable.
Either a JSON scalar value or a SQL/JSON variable, followed by a comparison predicate, followed by a relative simple path expression.
A JSON scalar value, followed by a comparison predicate, followed by another JSON scalar value.
A comparison predicate is ==, !=, <, <=, >=, or >.
A SQL/JSON variable is a dollar sign ($) followed by the name of a SQL identifier that is bound in a PASSING clause for json_exists.
The predicates that you can use in filter conditions are thus &&, ||, !, exists, ==, !=, <, <=, >=, and >.
As an example, the filter condition (a || b) && (!(c) || d < 42) is satisfied if both of the following criteria are met:
At least one of the filter conditions a and b is satisfied: (a || b).
Filter condition c is not satisfied or the number d is less than or equal to 42, or both are true: (!(c) || d < 42).
Comparison predicate ! has precedence over &&, which has precedence over ||. You can always use parentheses to control grouping.
Without parentheses for grouping, the preceding example would be a || b && !(c) || d < 42, which would be satisfied if at least one of the following criteria is met:
Condition b && !(c) is satisfied, which means that each of the conditions b and !(c) is satisfied (which in turn means that condition c is not satisfied).
Condition a is satisfied.
Condition d < 42 is satisfied.
A relative simple path expression is an at sign (@) followed by zero or more path steps. The at sign represents the path-expression current filter item, that is, the JSON data that matches the part of the (surrounding) path expression that precedes the filter. The simple path expression is matched against the current filter item in the same way that a path expression is matched against the context item.
A simple path expression is either an absolute simple path expression or a relative simple path expression. (The former begins with $; the latter begins with @.)

$ – The context item.
$.friends – The value of field friends of a context-item object. The dot (.) immediately after the dollar sign ($) indicates that the context item is a JSON object.
$.friends[0] – An object that is the first element of an array that is the value of field friends of a context-item object. The bracket notation indicates that the value of field friends is an array.
$.friends[0].name – Value of field name of an object that is the first element of an array that is the value of field friends of a context-item object. The second dot (.) indicates that the first element of array friends is an object (with a name field).
$.friends[*].name – Value of field name of each object in an array that is the value of field friends of a context-item object.
$.*[*].name – Field name values for each object in an array value of a field of a context-item object.
$.friends[3, 8 to 10, 12] – The fourth, ninth through eleventh, and thirteenth elements of an array friends (field of a context-item object). The elements must be specified in ascending order, and they are returned in that order: fourth, ninth, tenth, eleventh, thirteenth.
$.friends[3].cars – The value of field cars of an object that is the fourth element of an array friends. The dot (.) indicates that the fourth element is an object (with a cars field).
$.friends[3].* – The values of all of the fields of an object that is the fourth element of an array friends.
$.friends[3].cars[0].year – The value of field year of an object that is the first element of an array that is the value of field cars of an object that is the fourth element of an array friends.
$.friends[3].cars[0]?(@.year > 2014) – The first object of an array cars (field of an object that is the fourth element of an array friends), provided that the value of its field year is greater than 2014.
$.friends[3]?(@.addresses.city == "San Francisco") – An object that is the fourth element of an array friends, provided that it has an addresses field whose value is an object with a field city whose value is the string "San Francisco".
$.friends[3]?(@.addresses.city == "San Francisco" && @.addresses.state == "Nevada") – Objects that are the fourth element of an array friends, provided that there is a match for an address with a city of "San Francisco" and there is a match for an address with a state of "Nevada".
Note: The filter conditions in the conjunction do not necessarily apply to the same object — the filter tests for the existence of an object with city San Francisco and for the existence of an object with state Nevada. It does not test for the existence of an object with both city San Francisco and state Nevada. See Using Filters with JSON_EXISTS.
$.friends[3].addresses?(@.city == "San Francisco" && @.state == "Nevada") – An object that is the fourth element of array friends, provided that object has a match for city of "San Francisco" and a match for state of "Nevada". 



• https://mariadb.com/kb/en/json-functions/



Differences between JSON_QUERY and JSON_VALUE - Comparison between and examples with JSON_QUERY and JSON_VALUE.
JSONPath Expressions - MariaDB JSONPath description and definition.
JSON_ARRAY - Returns a JSON array containing the listed values.
JSON_ARRAYAGG - Returns a JSON array containing an element for each value in a given set of JSON or SQL values.
JSON_ARRAY_APPEND - Appends values to the end of the given arrays within a JSON document.
JSON_ARRAY_INSERT - Inserts a value into a JSON document.
JSON_COMPACT - Removes all unnecessary spaces so the json document is as short as possible.
JSON_CONTAINS - Whether a value is found in a given JSON document or at a specified path within the document.
JSON_CONTAINS_PATH - Indicates whether the given JSON document contains data at the specified path or paths.
JSON_DEPTH - Maximum depth of a JSON document.
JSON_DETAILED - Represents JSON in the most understandable way emphasizing nested structures.
JSON_EQUALS - Checks if there is equality between two json objects.
JSON_EXISTS -   Determines whether a specified JSON value exists in the given data.
JSON_EXTRACT -  Extracts data from a JSON document.
JSON_INSERT -  Inserts data into a JSON document.
JSON_KEYS - Returns keys from top-level value of a JSON object or top-level keys from the path.
JSON_LENGTH -  Returns the length of a JSON document, or the length of a value within the document.
JSON_LOOSE -  Adds spaces to a JSON document to make it look more readable.
JSON_MERGE -  Merges the given JSON documents.
JSON_MERGE_PATCH - RFC 7396-compliant merge of the given JSON documents.
JSON_MERGE_PRESERVE - Synonym for JSON_MERGE.
JSON_NORMALIZE - Recursively sorts keys and removes spaces, allowing comparison of json documents for equality.
JSON_OBJECT - Returns a JSON object containing the given key/value pairs.
JSON_OBJECTAGG - Returns a JSON object containing key-value pairs.
JSON_OVERLAPS - Returns true if two json documents have at least one key-value pair or array element in common.
JSON_QUERY - Given a JSON document, returns an object or array specified by the path.
JSON_QUOTE - Quotes a string as a JSON value.
JSON_REMOVE -  Removes data from a JSON document.
JSON_REPLACE - Replaces existing values in a JSON document.
JSON_SEARCH - Returns the path to the given string within a JSON document.
JSON_SET - Updates or inserts data into a JSON document.
JSON_TABLE - Given data from a JSON document, returns a representation of it as a relational table.
JSON_TYPE - Returns the type of a JSON value.
JSON_UNQUOTE - Unquotes a JSON value, returning a string.
JSON_VALID - Whether a value is a valid JSON document or not.
JSON_VALUE - Given a JSON document, returns the specified scalar.



• https://stackoverflow.com/questions/55930546/importing-json-file-to-mysql-5-7



LOAD_FILE



• https://stackoverflow.com/questions/65263965/how-to-import-json-values-inside-mysql-10-2-36-mariadb-table



CREATE TABLE.... WITH RECURSIVE ...  CTE1 ... CTE2  for MariaDB 10.2, I use 10.5



• https://mariadb.com/resources/blog/mariadb-platform-x5-new-tools-and-features-for-developers/,
  https://mariadb.com/resources/blog/mariadb-10-2-beta/



Enhanced SQL, CTE, VIEWs can now include subqueries in the FROM clause and so on



• https://github.com/MicrosoftDocs/sql-docs/tree/live/docs/relational-databases/json



MS Example
add-a-root-node-to-json-output-with-the-root-option-sql-server.md
convert-json-data-to-rows-and-columns-with-openjson-sql-server.md
format-json-output-automatically-with-auto-mode-sql-server.md
format-nested-json-output-with-path-mode-sql-server.md
format-query-results-as-json-with-for-json-sql-server.md
how-for-json-converts-sql-server-data-types-to-json-data-types-sql-server.md
how-for-json-escapes-special-characters-and-control-characters-sql-server.md
import-json-documents-into-sql-server.md
include-null-values-in-json-include-null-values-option.md
index-json-data.md
json-data-sql-server.md
json-path-expressions-sql-server.md
optimize-json-processing-with-in-memory-oltp.md
remove-square-brackets-from-json-without-array-wrapper-option.md
solve-common-issues-with-json-in-sql-server.md
store-json-documents-in-sql-tables.md
use-for-json-output-in-sql-server-and-in-client-apps-sql-server.md
use-openjson-with-an-explicit-schema-sql-server.md
use-openjson-with-the-default-schema-sql-server.md
validate-query-and-change-json-data-with-built-in-functions-sql-server.md

• (2023) CloudflareWorker and Supabase
• (2022) NativeScript, AndroidBooks
• (2022) JS, Css
• (2022) Typescript, Webpack
• (2022) Angular, RxJs, Firebase, MongoDb
• (2022) Node, NestJs, Electron, Pwa, Telegram
• (2022) React, Redux, GraphQL, NextJs
• (2022) Angular/Typescript, JS books

Comments ( )