Transact SQL – JSON Functions – OPENJSON

Background

The OpenJSON function allows us to shred a JSON Object into relational rows.

Legend

Here are earlier posts on Transact SQL & JSON :-

  1. Transact SQL – JSON Functions – JSON_VALUE & JSON_QUERY
    Link

JSON Data

Code


{
      "id":1

    , "name":"McDonalds"

    , "dish":
         [
              "Big McDonald"
            , "French Fries"
         ]

    , "storeLocation":
         {
            "store":
            {
                "id":201
              , "streetname":"1819 Wilmington Drive"
              , "city":"Wilmington"
              , "state":"DE"
            }
         }

}

Declare Table Variable & Add data

SQL

set nocount on
go

declare @tbl TABLE
(
    [restaurantChain] nvarchar(4000)
)

insert into @tbl
(
    [restaurantChain]
)
select
     '{
              "id":1
            , "name":"McDonalds"
            , "dish":
                 [
                      "Big McDonald"
                    , "French Fries"
                 ]
            , "storeLocation":
                 {
                    "store":
                    {
                        "id":201
                      , "streetname":"1819 Wilmington Drive"
                      , "city":"Wilmington"
                      , "state":"DE"
                    }
                 }

        }'

union all

select
     '{
              "id":2

            , "name":"Papa John"

            , "dish":
                 [
                      "Pizza"
                    , "Chicken Wings"
                 ]

            , "storeLocation":
                 {
                    "store":
                    {
                        "id":201
                      , "streetname":null
                      , "city":"Beverly Hills"
                      , "postalCode": "90210"
                      , "state": "CA"
                    }
                 }

        }'

union all

select
     '{
              "id":3
            , "name":"Popeyes"
            , "dish":
                 [
                      "Jabalaya"
                    , "Chicken Sandwich"
                 ]
            , "storeLocation":
                 {
                    "store":
                    {
                        "id":1
                      , "streetname":"899 Cleveland Street"
                      , "city":"Atlanta"
                      , "state":"GA"
                    }
                 }
        }'

        ;

Output

Queries

Query -01

Outline

In our first example, we issue openjson against the restaurantChain column.

We receive the following columns back :-

  1. id
    • path $.id
    • datatype :- bigint
  2. name
    • path $.name
    • datatype :- nvarchar(100)
  3. dish
    • path :- $.dish
    • datatype :- json
  4. storeLocation
    • path :- $.storeLocation
    • datatype :- json

SQL


select

        tblRC.*

from @tbl tbl

cross apply openjson
            (
                tbl.restaurantChain
            )

with
    (
          [id]                  bigint           '$.id'

        , [name]                varchar(100)     '$.name'

        , [dish]                nvarchar(max)    '$.dish' as json

        , [storeLocation]       nvarchar(max)    '$.storeLocation' as json

    ) tblRC

Output

json.sample1.openjson.20190829.1231AM

Query – 02

Outline

In our second example, we issue openjson against the restaurantChain column.

We receive the following columns back :-

  1. id
    • path $.id
    • datatype :- bigint
  2. name
    • path $.name
    • datatype :- nvarchar(100)
  3. storeLocation
    • path :- $.storeLocation
    • datatype :- json

And, a secondary openjson against the storeLocation column.

For this second query we pass along .$store as the path.

We receive back :-

  1. id ( storeLocationID)
    • path $.id
    • datatype :- bigint
  2. streetName
    • path $.streetname
    • datatype :- nvarchar(100)
  3. city
    • path :- $.city
    • datatype :- nvarchar(100)
  4. state
    • path :- $.state
    • datatype :- nvarchar(100)
  5. postalCode
    • path :- $.postalCode
    • datatype :- nvarchar(100)

SQL


select

          [restaurantID]
                = tblSL.id

        , [restaurant]
                = tblSL.[name]

        , [storeID]
            = tblSLI.[id]

        , [storeStreetName]
            = tblSLI.[streetname]

        , [storeCity]
            = tblSLI.[city]

        , [storePostalCode]
            = tblSLI.[postalCode]

        , [storeState]
            = tblSLI.[state]

from @tbl tbl

cross apply openjson
            (
                  tbl.restaurantChain
            ) 

    with
        (
                [id]                bigint          '$.id'

                , [name]            nvarchar(100)   '$.name'    

                , [storeLocation]   nvarchar(max)   '$.storeLocation' as json

        ) tblSL

cross apply openjson
            (
                  tblSL.[storeLocation]
                , '$.store'
            ) 

    with
        (

                  [id]              bigint           '$.id'
                , [streetName]      nvarchar(400)    '$.streetname'
                , [city]            nvarchar(400)    '$.city'
                , [state]           nvarchar(400)    '$.state'
                , [postalCode]      nvarchar(400)    '$.postalCode'

        ) tblSLI

Output

json.sample1.openjson.and.openjson.20190829.1231AM

Source Code

GitHub

Repository

DanielAdeniji/joinTransactSQLSample
Link

 

Dedicated

Dedicating to my homie, Ed Schavelev.

Got in a crunch trying to make sense of OpenJSON.

And, thankfully Ed Schavelev has the most straightforward example and explanation.

 

Summary

So there you have it.

As a new School year is getting started, we have our sophomore post out of the way.

References

  1. Microsoft
    • Docs

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s