<< 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 .
SELECT [value] ,[type],[key] FROM OPENROWSET (BULK 'C:\sample-json-file.json', SINGLE_CLOB) as JsonFile CROSS APPLY OPENJSON(BulkColumn)
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
XML Data Type and Columns (SQL Server) XML Indexes (SQL Server) XML Schema Collections (SQL Server) FOR XML (SQL Server) OPENXML (Transact-SQL)
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.
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')
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.
CREATE TABLE.... WITH RECURSIVE ... CTE1 ... CTE2 for MariaDB 10.2, I use 10.5
Enhanced SQL, CTE, VIEWs can now include subqueries in the FROM clause and so on
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
Comments ( )
<00> <01> <02> <03> <04> <05> <06> <07> <08> <09> <10> <11> <12> <13> <14> <15> <16> <17> <18> <19> <20> <21>
Link to this page: http://www.vb-net.com/PWA/SqlJson.htm
|<SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME>|