Transact SQL – JSON Functions – JSON_VALUE & JSON_QUERY

Background

Whenever I see JSON mentioned I tighten up a bit, knowing it is one more thing to learn, and I just move on.

Transact SQL

Let us address how to use a couple of JSON Functions in Transact SQL.

VALUE & QUERY

Both functions are the same outside of the fact that one returns scalar values and the other returns list.

JSON Data

Text


{
      "id":1

    , "name":"McDonalds"

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

    , "address":
     {
        "Store":
        {
            "id":201
          , "streetname":"1819 Wilmington Drive"
        }
     }
} 

Transact SQL

Outline

  1. To get
    • Scalar Values, use JSON_VALUE
      • id, we issue JSON_VALUE( column, ‘$.id’)
      • name, we issue JSON_VALUE( column, ‘$.name’)
    • Compound Values, use JSON_QUERY
      • id, we issue JSON_QUERY( column, ‘$.dish’)
      • name, we issue JSON_QUERY( column, ‘$.address’)

 

Code


set nocount on
go

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

insert into @tbl
select
     '{
              "id":1
            , "name":"McDonalds"
            , "dish":
                 [
                      "Big McDonald"
                    , "French Fries"
                 ]
            , "address":
                 {
                    "Store":
                    {
                        "id":201
                      , "streetname":"1819 Wilmington Drive"
                    }
                 }

        }'

union all

select
     '{
              "id":2

            , "name":"Papa John"

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

            , "address":
                 {
                    "Store":
                    {
                        "id":201
                      , "streetname":null
                      , "postalCode": "90210"
                      , "city":"Beverly Hills"
                    }
                 }

        }'

union all

select
     '{
              "id":3
            , "name":"Popeyes"
            , "dish":
                 [
                      "Jabalaya"
                    , "Chicken Sandwich"
                 ]
            , "address":
                 {
                    "Store":
                    {
                        "id":1
                      , "streetname":"899 C Street"
                    }
                 }
        }'

        ;

select

          [id]
          = JSON_VALUE
           (
                tbl.bio, '$.id'
            )

         , [name]
          = JSON_VALUE
           (
                tbl.bio, '$.name'
            )

          , [dish]
            = JSON_QUERY
            (
                tbl.bio, '$.dish'
            )

          , [address]
            = JSON_QUERY
            (
                tbl.bio, '$.address'
            )

from @tbl tbl

Output

json.sample1.20190828.0718PM.PNG

Source Code

GitHub

Repository

DanielAdeniji/joinTransactSQLSample
Link

File

DanielAdeniji/joinTransactSQLSample – JSON_VALUE & JSON_QUERY
Link

References

  1. Microsoft
    • Transact SQL

One thought on “Transact SQL – JSON Functions – JSON_VALUE & JSON_QUERY

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