Built-in JSON Support in SQL SERVER 2014

By Admin at 5 Aug 2016, 19:06 PM
  • What is JSON?

    JSON (JavaScript Object Notation) stores data in an organized manner, making it easier to access the details. This is a very popular data format used to transfer data in web and mobile applications.

    Data transmission should be done quickly and asynchronously without affecting the page rendering speed. The JSON structure transfers data without impacting a user’s interaction and awareness.

    Here is an example of JSON data that stores personal information.

    Common Uses of JSON:

    1. Store unstructured data in log files
    2. Store data in NoSQL databases
    3. Send and receive data in REST Web services
    4. REST endpoints that accept/return JSON are built into Azure Web services like Azure Storage and Azure Search
    5. Asynchronous calls for data transfer between web pages and servers

    Microsoft introduced built-in JSON functions in SQL Server to align with the web services that use JSON for their data transmission.

    • Parse JSON format data
    • Read or modify values
    • Transform arrays of objects in JSON format to a table
    • Transform results from a table into JSON Format

    JSON Path Expressions:

    JSON paths identify the properties of JSON objects. The syntax for JSON paths are similar to Jscript:

    Defining a path consists of two components:

    1. Path mode (optional) – should contain one of these mode values:
      1. Lax (default mode): If the path expression has an error, then the function will return an empty value. This is the default mode.
      2. Strict: If the path expression has an error then the function will return an error.
    2. Path – a set of path steps specified using the following operators and elements:
    3. Key names: If the key names contain any special character, it should be surrounded by quotation marks. For example, $.”user name”.
    4. Context item: Represented by a dollar sign ($).
    5. Array elements: Arrays are zero-based. For example, $.name[3].
    6. Member of an object: Represented by the dot operator.

    Available Built-in Functions:

    Function Name

    Purpose

    Syntax

    More details

    ISJSON

    Checks whether a valid JSON available in the given stringISJSON (<string for test JSON>)

    Returns 1 if it is JSON, 0 otherwise

    ISJSON() can also be used in SELECT queries to select a result set based on the value return from ISJSON() function.

    JSON_VALUE

    Extracts a scalar (single) value from a JSON StringJSON_VALUE (expression, path) Expression: a variable or a table column containing JSON text.· Prompts an error if the given expression is invalid

    Path: specifies the property to be extracted from the JSON expression· Prompts an error if the given path is in an invalid format

    Returns the outputasnvarchar (4000)

    If the value of the output is greater than 4000 characters, JSON_VALUE:

    • returns null in lax mode
    • will return an error in strict mode

    JSON_QUERY

    Extracts an array or object from the given JSON stringJSON_QUERY (expression, path) Returns the outputasnvarchar(max)

    If the value is not an array or object, JSON_QUERY:· returns null in lax mode· returns an error in strict mode

    JSON_MODIFY

    Updates a property value in the specified JSON string and returns the updated stringJSON_MODIFY (expression, path, value)
    Path: specifies the property to be updated

    Syntax:

    [append] [lax | strict] $.<json.path>

    • Append - specifies the new value to be appended
    • Lax [default mode]- specifies the property to be inserted
    • Strict - specifies the property referenced for updates
    • <json.path> - specifies the path for the property to be updated

    New value: the new value for the property in the specified JSON path.

    - Lax mode: JSON_MODIFY deletes the specified key if the new value is NULL.

    Returns the updated and formatted JSON text

    OPENJSON

    Returns the given JSON text in a collection of rows and columns.OPENJSON(@json) Runs SQL queries on a JSON collection

    Imports JSON text to SQL tables

    Two modes:

    • OPENJSON without schema

    In this mode, the function parses the details and returns the results as
    Key –Value pairs along with the Type details [String, Number, etc.] as three columns.

    • OPENJSON with schema

    With Schema: the function returns a table with the specified columns as per the specified schema for the output.

    Accepts single or collection of JSON objects

    Converts JSON array’s each element into a new row in the output table.

    About FOR JSON:

    FOR JSON is a feature used to format query results as JSON.

    Add this clause to a SELECT Statement to export the data from SQL Server in JSON.

    You can explicitly specify the JSON structure or leave it to the SQL Server SELECT statement to determine the structure of the output.

    There are two modes available with the FOR JSON Clause:

    - Path mode: JSON format of the output is mentioned by the programmer

    - Auto mode: JSON format of the output is determined by SQL Server


    Comments

     

    Post a comment

    Please correct the following: