scottgriv/sql-mssql_openjson_example

An example query that demonstrates how to use the OPENJSON function in Microsoft SQL Server.

---

Microsoft SQL Server OPENJSON Example

An example query that demonstrates how to use the OPENJSON function in Microsoft SQL Server.

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. OPENJSON provides a rowset view over a JSON document that is useful for applying relational operators like PIVOT and UNPIVOT. OPENJSON can be used as the target of an INSERT, UPDATE, or DELETE statement, just like a regular table or view.

---

Table of Contents

• Getting Started
  • Question
  • My Answer
• Resources
• License
• Credits

Getting Started

The openjson_function.sql file found in the root of this repository contains a T-SQL script that demonstrates how to use the OPENJSON function in Microsoft SQL Server 2016 and above. The xml_function.sql file found in the root of this repository contains a T-SQL script that demonstrates how to use the XML functions in Microsoft SQL Server 2014.

The query was created as an answer for a question on Stack Overflow on 09/14/22 that I answered called How to perform a two column split by linking your data in sql server.

I hope you find this example useful and it helps you learn how to use OPENJSON in your own SQL queries. Feel free to reference the Fiddle I created to help answer the question.

Question

My db is on SQL server

I have a situation where I have a column that brings me the phase of my card and a column that brings me the date that this card entered the phase, but they are in the format of an "Array". How can I link the phase name with its entry date via query?

card_id	phase_history	firstTimeIn
8837	[Start, Compliance, Contact, Down]	[2022-08-11T13:44:24+00:00, 2022-08-11T13:44:25+00:00, 2022-08-25T17:37:19+00:00, 2022-08-11T13:44:26+00:00]
6596	[Start, Compliance, Contact, Down]	[2022-03-11T13:44:24+00:00, 2022-04-11T13:44:25+00:00, 2022-04-25T17:37:19+00:00, 2022-04-11T13:44:26+00:00]
2416	[Start, Contact, Up]	[2022-08-02T02:22:31+00:00, 2022-08-02T02:22:31+00:00, 2022-08-02T12:13:32+00:00]
5424	[Start, Compliance, Contact, Lead, Down]	[2022-09-01T12:51:24+00:00, 2022-09-01T12:51:25+00:00, 2022-09-01T13:25:52+00:00, 2022-09-01T12:51:26+00:00, 2022-09-01T16:47:31+00:00]

I would like it to be like this:

card_id	phase_history	firstTimeIn
8837	Start	2022-08-11T13:44:24+00:00
8837	Compliance	2022-08-11T13:44:25+00:00

If anyone has any other suggestions on how to handle this I would appreciate it.

My Answer

You can use OPENJSON to convert the array rows into new rows by the card_id column, and use TRIM to remove any extraneous brackets and whitespaces.

/*  For SQL Server 2016 (compatibility level 130+) and higher */
SELECT d.card_id, a.phase_history, a.firstTimeIn
FROM cards d
CROSS APPLY (
   SELECT 
   TRIM('[ ]' FROM c.[value]) AS phase_history, 
   TRIM('[ ]' FROM s.[value]) AS firstTimeIn
   FROM OPENJSON(CONCAT('["', REPLACE(d.phase_history, ',', '","'), '"]')) c
   LEFT OUTER JOIN OPENJSON(CONCAT('["', REPLACE(d.firstTimeIn, ',', '","'), '"]')) s 
      ON c.[key] = s.[key]
) a 
WHERE a.phase_history IN ('Start', 'Compliance')
AND card_id = 8837

Result Set:

card_id	phase_history	firstTimeIn
8837	Start	2022-08-11T13:44:24+00:00
8837	Compliance	2022-08-11T13:44:25+00:00

The real solution, however, would be to adjust your database design. Storing multiple values in an array format will cause you a lot of problems in the future. Fixing it now rather than later will prevent future headaches.

db<>fiddle here.

Update

I've been recently asked to do this with an older version of SQL Server (SQL Server 2014). Because OPENJSON is only available in SQL Server 2016 (compatibility level 130 or higher), you should use a combination of XML functions, CTEs, and CROSS APPLY to achieve the same results as above.

/*  For SQL Server 2014 */
WITH PhaseHistoryCTE AS (
    SELECT 
        d.card_id,
        LTRIM(RTRIM(x.value('.', 'VARCHAR(50)'))) AS phase_history,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM 
        cards d
    CROSS APPLY (
        SELECT CAST('<x>' + REPLACE(SUBSTRING(d.phase_history, 2, LEN(d.phase_history) - 2), ',', '</x><x>') + '</x>' AS XML) AS xmlValues
    ) AS a
    CROSS APPLY xmlValues.nodes('/x') AS SplitLoad(x)
),
FirstTimeInCTE AS (
    SELECT 
        d.card_id,
        LTRIM(RTRIM(x.value('.', 'VARCHAR(50)'))) AS firstTimeIn,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM 
        cards d
    CROSS APPLY (
        SELECT CAST('<x>' + REPLACE(SUBSTRING(d.firstTimeIn, 2, LEN(d.firstTimeIn) - 2), ',', '</x><x>') + '</x>' AS XML) AS xmlValues
    ) AS b
    CROSS APPLY xmlValues.nodes('/x') AS SplitBOL(x)
)
SELECT 
    p.card_id, 
    p.phase_history, 
    f.firstTimeIn
FROM 
    PhaseHistoryCTE p
LEFT OUTER JOIN 
    FirstTimeInCTE f
ON 
    p.rn = f.rn
WHERE 
    p.phase_history IN ('Start', 'Compliance')
AND 
    p.card_id = 8837;

db<>fiddle here.

Resources

• Stack Overflow
• How to perform a two column split by linking your data in sql server
• Answer Example Fiddle
• Microsoft SQL Server - 2022 Home
• Microsoft SQL Server - 2022 Documentation
• Microsoft SQL Server - Downloads
• Microsoft SQL Server - OPENJSON
• Microsoft Excel
• JSON
• JSON - Wikipedia
• JSON - RFC 8259

License

This project is released under the terms of The Unlicense, which allows you to use, modify, and distribute the code as you see fit.

• The Unlicense removes traditional copyright restrictions, giving you the freedom to use the code in any way you choose.
• For more details, see the LICENSE file in this repository.

Credits

Author: Scott Grivner
Email: [email protected]
Website: scottgrivner.dev
Reference: Main Branch

---