[SQL2016] JSON DML 및 Cross Apply를 통한 항목 추출

/*********************************************************************************************************
-- Title : [SQL2016] JSON DML 및 Cross Apply를 통한 항목 추출
-- Reference : http://sqlsql.tistory.com/504
-- Key word : json for json auto openjson cross apply
*********************************************************************************************************/

-- SQL

-- 테이블 생성 IF OBJECT_ID('TBLX') IS NOT NULL DROP TABLE TBLX; GO CREATE TABLE TBLX ( id INT NOT NULL IDENTITY, C1 VARCHAR(100), CJSON NVARCHAR(MAX) CHECK ( ISJSON ( CJSON ) = 1 ) ); GO -- JSON INSERT ;WITH CTE_P AS ( SELECT DISTINCT LASTWAITTYPE FROM MASTER.DBO.SYSPROCESSES ) INSERT INTO TBLX (C1, CJSON) SELECT LASTWAITTYPE , SYSPROCESSESJSON = ( SELECT SPID, KPID, WAITTYPE FROM MASTER.DBO.SYSPROCESSES C WHERE P.LASTWAITTYPE = C.LASTWAITTYPE FOR JSON AUTO ) FROM CTE_P P; GO SELECT * FROM tblx; -- JSON UPDATE SELECT * from tblx WHERE C1 = 'LAZYWRITER_SLEEP'; UPDATE TBLX SET CJSON = '[{"SPID":6,"KPID":10348,"WAITTYPE":"AKW="},{"SPID":15,"WAITTYPE":"AKW="}]' WHERE C1 = 'LAZYWRITER_SLEEP'; SELECT * from tblx WHERE C1 = 'LAZYWRITER_SLEEP'; -- JCON 항목 추출 -- 항목이 멀티값일 때 ROW가 분할 됨 SELECT * FROM TBLX A CROSS APPLY OPENJSON (CJSON) WITH ( SPID BIGINT , KPID BIGINT , WAITTYPE VARCHAR(100) ) AS J;