JSON#

JSON is a popular textual data format that’s used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases. Many REST web services return results that are formatted as JSON text or accept data that’s formatted as JSON.

JSON functions enable you to combine NoSQL and relational concepts in the same database. Now you can combine classic relational columns with columns that contain documents formatted as JSON text in the same table, parse and import JSON documents in relational structures, or format relational data to JSON text.

Here’s an example of JSON text:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

By using built-in functions and operators, you can do the following things with JSON text:

Parse JSON text and read or modify values,
Transform arrays of JSON objects into table format,
Run any Transact-SQL query on the converted JSON objects,
Format the results of Transact-SQL queries in JSON format.

Key JSON capabilities#

Extract values from JSON text and use them in queries#

If you have JSON text that’s stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

ISJSON tests whether a string contains valid JSON,
JSON_QUERY extracts an object or an array from a JSON string,
JSON_VALUE extracts a scalar value from a JSON string.

In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table called People:

SELECT Name,
       Surname,
       JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
       JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
           + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
       JSON_QUERY(jsonCol, '$.info.skills') AS Skills
  FROM People
 WHERE ISJSON(jsonCol) > 0
   AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
   AND STATUS = 'Active'
 ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). JSON functions use JavaScript-like syntax for referencing values inside JSON text.

Convert data to JSON or export JSON#

Format data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON to delegate the formatting of JSON output from your client applications to Querona.

The following example uses PATH mode with the FOR JSON clause:

SELECT id,
       firstName AS "info.name",
       lastName AS "info.surname",
       age,
       dateOfBirth AS dob
  FROM People
   FOR JSON PATH;

The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Use cases for JSON data in Querona#

JSON support in Querona lets you combine relational and NoSQL concepts. You can easily transform relational to semi-structured data and vice-versa. JSON isn’t a replacement for existing relational models, however. Here are some specific use cases that benefit from the JSON support in Querona.

Simplify complex data models#

Consider denormalizing your data model with JSON fields in place of multiple child tables.

Store retail and e-commerce data#

Store info about products with a wide range of variable attributes in a denormalized model for flexibility.

Process log and telemetry data#

Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language.

Store semi-structured IoT data#

When you need real-time analysis of IoT data, load the incoming data directly into the database instead of staging it in a storage location.

Simplify REST API development#

Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site.

Combine relational and JSON data#

Querona provides a hybrid model for obtaining and processing both relational and JSON data by using standard Transact-SQL language. You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables by using Transact-SQL.

JSON

Contents