1 of 30

Query JSON in SQL Server

Aaron

Codesanook

2 of 30

Aaron

  • Admin Codesanok page:
    • fb.com/codesanookpage�
  • Admin .NET Thailand group:

​

  • Open source projects:

3 of 30

4 of 30

Agenda

  • Things that we cover:
    • What Is JSON?
    • Querying JSON with T-SQL
    • Converting Data into JSON
    • Update JSON data�
  • Things that we do not cover:
    • Parsing and Importing JSON
    • Index JSON data

​

5 of 30

Knowledge contribution

dotnetthailand.com

  • fb.com/codesanookpage
  • > 21K FB likes
  • .NET Thailand group
  • fb.com/groups/dotnetthailand
  • > 8.3K members

6 of 30

Code example

https://github.com/dotnetthailand/orchard-core-blog-example

​

7 of 30

What is JSON?

8 of 30

9 of 30

What is JSON?

  • JavaScript Object Notation
  • data format that's used for exchanging data
    • web app
    • mobile app
    • AJAX calls, client - server
    • REST API
    • GraphQL response
  • light weight
  • Human-readable
  • Language independent

{

"id": 1,

"firstName": "Jose",

"lastName": "Realman",

"dateOfBirth":"2012-04-23T18:25:43.511Z"

}

10 of 30

JSON in SQL server

  • Starting from SQL Server 2016
  • Supported by Azure SQL Database
  • Store data as VARCHAR or NVARCHAR column (unicode)
    • VARCHAR (Variable-length, non-Unicode character data) size is upto 8,000 characters (1 byte per character).
    • NVARCHAR (Variable-length, non-Unicode character data) size is upto 4,000 characters (2 bytes per character).
    • NVARCHAR(MAX) size is upto 2 GB of storage

11 of 30

Alternative solutions

  • MongoDB
  • JSON data type in Postgres
  • Azure Cosmo DB
  • Other NoSQL providers

12 of 30

JSON in real world

  • Explore a real world project that use JSON to store data
  • Orchard Core CMS
    • Why Orchard Core CMS?
  • We are going to use it as a demo project
    • Setup Orchard Core CMS

13 of 30

Orchard Core concept

https://www.dotnetthailand.com/web-frameworks/orchard-core-cms/why-orchard-core-cms

14 of 30

Prepare the demo project

https://github.com/dotnetthailand/orchard-core-blog-example

15 of 30

Prepare steps

  • Clone the project
    • git clone git@github.com:dotnetthailand/orchard-core-blog-example.git
  • Start the project
    • chomod +x start-app.sh
    • start-app.sh
  • Launch Docker container (SQL Server 2019)
    • docker compose up
  • Setup the website
  • Connect to SQL Server instance �(SQL Server (mssql) extension)

16 of 30

DEMO

setup the project�simple-query.sql

17 of 30

ISJSON

  • a function to test if a string contains valid JSON.
  • ISJSON(expression)
  • expression:
    • a variable or a column that contains JSON text
  • Returns 1 if the string contains valid JSON; otherwise, returns 0.
  • Returns null if expression is null.

18 of 30

DEMO

isjson.sql

19 of 30

JSON_VALUE

  • a function to retrieve a scalar value
  • JSON_VALUE (expression , path )
  • expression:
    • a variable or a column that contains JSON text
  • path:
    • JSON path that specifies a property to extract
    • e.g. $.ContentType, $.BlogPost.Image.Paths[0]
  • Return a single text value of type nvarchar(4000)

20 of 30

DEMO

json-value.sql

21 of 30

JSON_QUERY

  • a function to retrieve a JSON object
  • JSON_QUERY(expression , path )
  • expression:
    • a variable or a column that contains JSON text
  • path:
    • JSON path that specifies a property to extract
    • e.g. $.ContentType
  • Returns a JSON fragment of type nvarchar(max).

22 of 30

DEMO

json-query.sql

23 of 30

Convert query result to JSON

  • FOR JSON PATH clause
    • The FOR JSON clause formats SQL results as JSON
    • To share to other apps
    • The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results

SELECT

t.ContentItemId,

t.Title as 'TitlePart.Title',

h.Html as 'HtmlPart.Html'

FROM TitlePart t

INNER join HtmlBodyPart h

ON t.ContentItemId = h.ContentItemId

FOR JSON PATH;

24 of 30

Migrate Orchard version 1 to Orchard version 2

"TitlePart": {

"Title": "Hello world"

},

"MarkdownBodyPart": {

"Html": "Lorem ipsum dolor sit amet"

}

​

TitlePart table

  • Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  • Title NVARCHAR(255) NOT null,
  • ContentItemId INT NOT NULL

HtmlBodyPart table

  • Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
  • HtmlBodyPart NVARCHAR(MAX) NOT null,
  • ContentItemId INT NOT NULL

25 of 30

DEMO

create-tables.sql

for-json-path.sql

26 of 30

JSON_MODIFY

  • a function to update a property in JSON string
  • JSON_MODIFY(expression, path, newValue)
  • expression:
    • a variable or a column that contains JSON text
  • path:
    • JSON path that specifies a property to extract
    • e.g. $.TitlePath
  • newValue:
    • The new value for the property specified by path.
  • returns the updated JSON string

​

​

27 of 30

DEMO

json-modify.sql

28 of 30

Reference & resources

​

29 of 30

Question & Answer

It is not about being the best.

​

It is about always getting better.

30 of 30