Meet JSON In SQL Server 2016 Russ Loski

Meet JSON In SQL Server 2016 Russ Loski

Russ Loski SQL Server ETL developer from the Dallas Fort Worth area Member of the North Texas SQL Server Users Group Curious about structured data Regular speaker at SQL Saturdays Grand dad for active 5 year old Russ. Loski@SQLMovers. com www. SQLMovers. com @sqlmovers https: //www. linkedin. com/in/russloski

Structured Or Not Structured

Structured Or Not Structured <? xml version="1. 0"? > <catalog> <book id="bk 101"> <author>Gambardella, Matthew</author> <title>XML Developer's Guide</title> <genre>Computer</genre> <price>44. 95</price> <publish_date>2000 -10 -01 </publish_date> <description>An in-depth look at creating applications with XML. </description> </book> <book id="bk 102"> <author>Ralls, Kim</author> <title>Midnight Rain</title> <genre>Fantasy</genre> <price>5. 95</price> <publish_date>2000 -12 -16</publish_date> <description>A former architect battles corporate zombies, an evil sorceress, and her own childhood to become queen of the world. </description> </book>

Structured Or Not Structured policy. ID, statecode, county, eq_site_limit, hu_site_limit, fl_site_limit, fr_site_limit, tiv_2011, tiv_2012, eq_site_deductible, h 119736, FL, CLAY COUNTY, 498960, 498960, 792148. 9, 0, 9979. 2, 0, 0, 30. 102261, -81. 711777 448094, FL, CLAY COUNTY, 1322376. 3, 1438163. 57, 0, 0, 30. 063936 206893, FL, CLAY COUNTY, 190724. 4, 192476. 78, 0, 0, 30. 089579, -81. 70 333743, FL, CLAY COUNTY, 0, 79520. 76, 0, 0, 79520. 76, 86854. 48, 0, 0, 30. 063236, -81. 707703, Residential, Wood, 3 172534, FL, CLAY COUNTY, 0, 254281. 5, 246144. 49, 0, 0, 30. 060614, -81. 702675, Residentia

Structured Or Not Structured ISA*00* *12*ABCCOM *01*99999 *101127*1719*U*00400*000003438*0*P*> GS*PO*4405197800*99999*20101127*1719*1421*X*004010 VICS ST*834*0179 BGN*00*1*20050315*110650****4 REF*38*SAMPLE_POLICY_NUMBER DTP*303*D 8*20080321 N 1*P 5*COMPAN_NAME*FI*00000 INS*Y*18*030*20*A REF*0 F*SUBSCRIBER_NUMBER NM 1*IL*1*JOHN DOE*R***34*1*0000000 PER*IP**HP*2138051111 N 3*123 SAMPLE RD N 4*CITY*ST*12345 DMG*D 8*19690101 *F HD*030 DTP*348*D 8*20080101 REF*1 L*INDIV_POLICY_NO SE*16*0179 GE*1*1421 IEA*1*000003438

Structured Or Not Structured

Structured Or Not Structured Images Printed Books Book on line Web Page w/ Tables Comma Delimited JSON { "name"="Russ", "location"="Tyler" } XML OLTP DB with RDMS Pure Relational model

Microsoft Connect What is JSON?

Agenda What is JSON and why is it important Reading JSON in SQL Server Writing JSON in SQL Server

What is JSON Java. Script Object Notation Lightweight data format akin to XML Self-describing Converts directly to Java. Script objects Easy to work with in Java. Script than other formats What is JSON?

Io. T

Simple JSON {"employees": [ {"first. Name": "John", "last. Name": "Doe"}, {"first. Name": "Anna", "last. Name": "Smith"}, {"first. Name": "Peter", "last. Name": "Jones"} ]} What is JSON?

JSON Examples – Data Types Numbers: 1, 2, 3. 4 – No quotes Strings: "test" , "1", "true" – Double quotes Boolean: true, false – No quotes Array: [ , ] – Square brackets surrounding comma delimited list Object: {"name 1": "value 1", "name 2": "value 2"} Null: null – This indicates empty item. What is JSON?

JSON Examples - Arrays [ 1 , 2, 3, 4, 5] [ "Russ" , "Gail" , "Don" , "Julie"] [ {"phone. Type" : "cell" , "number" : "2145551111" } , {"phone. Type" : "home", "number" : "9725551111" } ] [ 1, "Russ", { "address 1" : "444 Main", "city" : "Tyler" } ] What is JSON?

Why JSON? Agile. No structure up front Light. Not the tag structure

Reading JSON in SQL Server 2016 Reading JSON into row set Extracting single value from JSON Identifying JSON Reading JSON

JSON Data Type? No JSON data type Use string (varchar, nvarchar up to max) Richer than XML support in early 2000 s Reading JSON

Reading JSON into Row Set OPENJSON ( NVARCHAR Column/Variable/Constant , JSON Path Expression ) Reading JSON

Open. JSON Examples SELECT * FROM OPENJSON (@var, N'$') SELECT * FROM tbl OUTER APPLY OPENJSON(tbl. Col, N'$. property') v SELECT * FROM tbl OUTER APPLY OPENJSON(tbl. Col, N'$. property) WITH ( Prop 1 VARCHAR(20) , Prop 2 INT N'$. Other. Property' , Prop 3 date N'strict $. Prop 3' ) as v Reading JSON

Reading value from JSON_Value ( NVARCHAR Column/Variable/Constant , JSON Path Expression ) Reading JSON

Reading JSON document from JSON_Query ( NVARCHAR Column/Variable/Constant , JSON Path Expression ) Reading JSON

JSON_Value/JSON_Query Examples SELECT JSON_Value (@var, N'$. property') SELECT JSON_Value( tbl. Col, N'$. property') FROM tbl SELECT JSON_Query (@var, N'$. property') SELECT JSON_Query( tbl. Col, N'$. property') FROM tbl Reading JSON

Demo Reading JSON

Writing JSON FOR JSON AUTO/PATH Options: INCLUDE_NULL_VALUES WITHOUT_ARRAY_WRAPPER ROOT('root') Writing JSON

Demo FOR JSON Writing JSON

Use Cases for JSON One-off queries Analysis of JSON data ETL. Loading JSON data into structured database Closing?

References https: //msdn. microsoft. com/en-US/library/dn 921897. aspx http: //www. sqlpass. org/24 hours/2016/edp/Sessions/Details. aspx? sid=49113 http: //www. sqlservercentral. com/articles/JSON/141175/ https: //dataonwheels. wordpress. com/2016/04/20/json-in-sql-server-2016 -the-goodthe-bad-and-the-ugly/