sql2016 json 데이터 넣고 빼기
IF OBJECT_ID('TBLX') IS NOT NULL
DROP TABLE TBLX
GO
CREATE TABLE TBLX (
C1 VARCHAR(100),
CJSON NVARCHAR(MAX) CHECK ( ISJSON ( CJSON ) = 1 )
)
GO
;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
UPDATE TBLX
SET CJSON = '[{"SPID":6,"KPID":10348,"WAITTYPE":"AKW="},{"SPID":15,"WAITTYPE":"AKW="}]'
WHERE C1 = 'LAZYWRITER_SLEEP'
SELECT *
FROM TBLX A
CROSS APPLY OPENJSON (CJSON)
WITH (
SPID BIGINT
, KPID BIGINT
, WAITTYPE VARCHAR(100)
) AS J
https://sqlwithmanoj.com/2015/06/01/exportconvert-table-or-sql-query-data-to-json-string-format-sql-server-2016-part-1/
을 시작으로 죽 읽어보면 도움이 많이 된다.
multi row 일 경우 json 컬럼에 인덱스를 만들수 없다.
index 는 그냥 computed column 에 일반 인덱스를 만들 뿐이다.
escape character
static string EscapeForJson(string s) {
string quoted = System.Web.Helpers.Json.Encode(s)
return quoted.Substring(1, quoted.Length - 2);
} public static string cleanForJSON(string s)
{
if (s == null || s.Length == 0) {
return "";
}
char c = '\0';
int i;
int len = s.Length;
StringBuilder sb = new StringBuilder(len + 4);
String t;
for (i = 0; i < len; i += 1) {
c = s[i];
switch (c) {
case '\\':
case '"':
sb.Append('\\');
sb.Append(c);
break;
case '/':
sb.Append('\\');
sb.Append(c);
break;
case '\b':
sb.Append("\\b");
break;
case '\t':
sb.Append("\\t");
break;
case '\n':
sb.Append("\\n");
break;
case '\f':
sb.Append("\\f");
break;
case '\r':
sb.Append("\\r");
break;
default:
if (c < ' ') {
t = "000" + String.Format("X", c);
sb.Append("\\u" + t.Substring(t.Length - 4));
} else {
sb.Append(c);
}
break;
}
}
return sb.ToString();
}-- 대소문자 가림
IF OBJECT_ID ('CUSTOMER') IS NOT NULL
DROP TABLE CUSTOMER
GO
CREATE TABLE DBO.CUSTOMER(
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
NAME NVARCHAR(50), DETAIL NVARCHAR(MAX))
GO
INSERT INTO DBO.CUSTOMER ( NAME, DETAIL )
SELECT TOP 200000 NEWID(),
REPLACE('{"ADDRESS":{"STATE":"KA","COUNTRY":"INDIA"},
"PHONE":"@PHONE"}',
'@PHONE', 100000000-ROW_NUMBER() OVER (ORDER BY SC1.OBJECT_ID))
FROM SYS.ALL_COLUMNS SC1
CROSS JOIN SYS.ALL_COLUMNS SC2
GO
SET STATISTICS IO ON
GO
SELECT *
FROM DBO.CUSTOMER
WHERE JSON_VALUE(DETAIL,'$.PHONE') = '99890000'
ALTER TABLE DBO.CUSTOMER
ADD PHONENUMBER AS JSON_VALUE(DETAIL,'$.PHONE')
SELECT TOP 10 * FROM DBO.CUSTOMER
CREATE INDEX IX_CUSTOMER_PHONENUMBER
ON DBO.CUSTOMER(PHONENUMBER)
GO
SELECT *
FROM DBO.CUSTOMER
WHERE JSON_VALUE(DETAIL,'$.PHONE') = '99890000'
GO
--(1개 행이 영향을 받음)
--테이블 'CUSTOMER'. 검색 수 1, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
SELECT * FROM CUSTOMER WHERE PHONENUMBER = '99890000'
GO
--(1개 행이 영향을 받음)
--테이블 'CUSTOMER'. 검색 수 1, 논리적 읽기 수 6, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
select switchoffset (ctime, '+09:00') ctime, ceventname, j.wait_type, j.duration, j.batch_text, j.statement, j.current_state, cjsondata
from txelog a
cross apply openjson (cjsondata)
with (
duration bigint
, batch_text varchar(max)
, statement varchar(max)
, wait_type varchar(100)
, current_state varchar(100)
) as j
order by cTime desc