A Cheat Sheet for SQL Server JSON

sql_json_cheatsheet.sql

-- -------------------------------------------------------------------
-- AUTHOR: Robby Boney
-- LICENSE: MIT
-- DESCRIPTION:
--      This is a simple cheat sheet for working with SQL Server JSON
--      data. It may not be complete but covers many of the aspects
--      I find useful to have examples for. For more details on SQL
--      Server JSON, see: 
--      https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15
-- -------------------------------------------------------------------


-- -----------------------------------
-- [EXAMPLE] DECLARE JSON AS A STRING
--           DATA Pulled from: http://api.nobelprize.org/v1/prize.json
-- -----------------------------------
DECLARE @json NVARCHAR(MAX);
SET @json = N'{
    "prizes": [{
      "year": "2021",
      "category": "chemistry",
      "laureates": [
        {
          "id": "1002",
          "firstname": "Benjamin",
          "surname": "List",
          "motivation": "\"for the development of asymmetric organocatalysis\"",
          "share": "2"
        },
        {
          "id": "1003",
          "firstname": "David",
          "surname": "MacMillan",
          "motivation": "\"for the development of asymmetric organocatalysis\"",
          "share": "2"
        }
      ]
    },
    {
      "year": "2021",
      "category": "economics",
      "laureates": [
        {
          "id": "1007",
          "firstname": "David",
          "surname": "Card",
          "motivation": "\"for his empirical contributions  to labour economics\"",
          "share": "2"
        },
        {
          "id": "1008",
          "firstname": "Joshua",
          "surname": "Angrist",
          "motivation": "\"for their methodological contributions to the analysis  of causal relationships\"",
          "share": "4"
        },
        {
          "id": "1009",
          "firstname": "Guido",
          "surname": "Imbens",
          "motivation": "\"for their methodological contributions to the analysis  of causal relationships\"",
          "share": "4"
        }
      ]
    }]
}';

-- ----------------------------------------------------
-- [REF] Path Expressions.
--       These are the supported ways of querying data
--       within a json varchar object.
-- ----------------------------------------------------
-- select root 
SELECT value FROM OPENJSON(@json,'$')

-- select root.year attribute 
SELECT value FROM OPENJSON(@json,'$.year')

-- select attribute from object at list index 0 
SELECT value FROM OPENJSON(@json,'$.laureates[0].firstname')

-- select attribute with special characters in key
SELECT value FROM OPENJSON(@custom,'$."custom example"')

-- check if value is a json formated object
SELECT value FROM CustomTable WHERE ISJSON(value) > 0


-- -----------------------------------------------------
-- [EXAMPLE] SELECT VALUES FROM EMBEDED OBJECTS
--           using the JSON_VALUE function
-- -----------------------------------------------------
SELECT
    JSON_VALUE  ( a.value,  '$.year'     ) as Year
    ,JSON_VALUE ( a.value,  '$.category' ) as Category
    ,JSON_VALUE ( b.value,  '$.id' ) as LaureateID
    ,JSON_VALUE ( b.value,  '$.firstname' ) as LaureateFirstName 
    ,JSON_VALUE ( b.value,  '$.surname' ) as LaureateLastName 
FROM OPENJSON(@json, '$.prizes') as a
CROSS APPLY OPENJSON(a.value, '$.laureates') as b


-- -----------------------------------------------------
-- [EXAMPLE] SELECT OBJECTS FROM EMBEDED OBJECTS
--           using the JSON_QUERY function
-- -----------------------------------------------------
SELECT
    JSON_VALUE  ( a.value,  '$.year'     ) as Year
    ,JSON_VALUE ( a.value,  '$.category' ) as Category
    ,JSON_QUERY ( a.value,  '$.laureates' ) as Laureates
FROM OPENJSON(@json, '$.prizes') as a


-- -----------------------------------------------------
-- [EXAMPLE] MODIFY VALUES DURING SELECTION
--           useful for renaming values
-- -----------------------------------------------------
SELECT
    JSON_VALUE  ( a.value,  '$.year'     ) as Year
    ,JSON_MODIFY(
        JSON_QUERY ( a.value,  '$' ), '$.category', 'custom category'
    )  as Category
FROM OPENJSON(@json, '$.prizes') as a