Tutorial by Examples: json

JSON_MODIFY function can be used to append new value to some array inside JSON: update Product set Data = JSON_MODIFY(Data, 'append $.tags', "sales") where ProductID = 17; New value will be appended at the end of the array, or a new array with value ["sales"] will be create...

If you have a "child table" formatted as JSON collection and stored in-row as JSON column, you can unpack this collection, transform it to table and join it with parent row. Instead of the standard JOIN operator, you should use CROSS APPLY. In this example, product parts are formatted as ...

In this example, Tags array may contain various keywords like ["promo", "sales"], so we can open this array and filter values: select ProductID, Name, Color, Size, Price, Quantity from Product CROSS APPLY OPENJSON(Data, '$.Tags') where value = 'sales' OPENJSON will op...

JSON is textual format, so it is stored in standard NVARCHAR columns. NoSQL collection is equivalent to two column key value table: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max) ) Use nvarchar(max) as you are not sure what would be the size of your JSON ...

Since JSON is stored textual column, you might want to ensure that it is properly formatted. You can add CHECK constraint on JSON column that checks is text properly formatted JSON: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max) CONSTRAINT [Data shoul...

You can expose values from JSON column as computed columns: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max), Price AS JSON_VALUE(Data, '$.Price'), Color JSON_VALUE(Data, '$.Color') PERSISTED ) If you add PERSISTED computed column, value from JSON tex...

Queries that filter or sort data by some value in JSON column usually use full table scan. SELECT * FROM ProductCollection WHERE JSON_VALUE(Data, '$.Color') = 'Black' To optimize these kind of queries, you can add non-persisted computed column that exposes JSON expression used in filter or sort...

If you can use memory-optimized tables, you can store JSON as text: CREATE TABLE ProductCollection ( Id int identity primary key nonclustered, Data nvarchar(max) ) WITH (MEMORY_OPTIMIZED=ON) Advantages of JSON in in-memory: JSON data is always in memory so there is no disk access Ther...

OPENJSON function parse JSON text and returns all key:value pairs at the first level of JSON: declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}'; SELECT * FROM OPENJSON(@json); keyvaluetypeNameJoe1age272ski...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Q...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. If the values in input object are nested, additional mapping parameter can be specified in each column in WITH clause: declare @json nvarchar(4000) = N'[ {"data":{"num":&quot...

OPENJSON can extract fragments of JSON objects inside the JSON text. In the column definition that references JSON sub-object set the type nvarchar(max) and AS JSON option: declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00"...

JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00", "Items":[{"Price"...

Prior to Json.NET 4.5 dates were written using the Microsoft format: "/Date(1198908717056)/". If your server sends date in this format you can use the below code to serialize it to NSDate: Objective-C (NSDate*) getDateFromJSON:(NSString *)dateString { // Expect date in this format ...

JSON Viewer SourceForge is a plugin for JSON visualization and formatting. It is useful for indenting /formatting JSON documents and can be used to browse complex JSON file using a treeview tool. The following image shows the commands offered by the plugin: Starting from an unformatted JSON frag...

A document can be created by using the CreateDocumentAsync method of the DocumentClient class. Documents are user defined (arbitrary) JSON content. async Task CreateFamilyDocumentIfNotExists(DocumentClient client, string databaseName, string collectionName, Family family) { try { ...

Suppose you have a pojo class Person public class Person { public String name; public Person(String name) { this.name = name; } } And you want to parse it into a JSON array or a map of Person objects. Due to type erasure you cannot construct classes of List<Person&g...

JSON Web Encryption (JWE) represents encrypted content using JavaScript Object Notation (JSON) based data structures. It defines a way to encrypt your claims data so that only intended receiver can read the information present in a token. In the JWE JSON Serialization, a JWE is represented as a JS...

By adding a header with content type as JSON: <?php $result = array('menu1' => 'home', 'menu2' => 'code php', 'menu3' => 'about'); //return the json response : header('Content-Type: application/json'); // <-- header declaration echo json_encode($result, true); // <--- e...

import groovy.json.JsonSlurper; def jsonSlurper = new JsonSlurper() def obj = jsonSlurper.parseText('{ "foo": "bar", "baz": [1] }') assert obj.foo == 'bar' assert obj.baz == [1]