Json ใช้ในระบบของ Javascript และ การติดต่อ ระหว่าง Cloud ระหว่างหลายภาษา เช่น python, java, dotnet. SQL Server 2017 มี Feature นี้ ด้วย
ก่อนอื่นต้อง Upgrade database ก่อน
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 130
ทดลอง Data ที่เป็น JSON
DECLARE @JSONData AS NVARCHAR(4000)
SET @JSONData = N'{
"EmployeeInfo":{
"FirstName":"Somchai",
"LastName":"Saema",
"Code":"CODED",
"Addresses":[
{ "Address":"1234", "City":"Bangkok", "State":THAILAND"},
{ "Address":"5678", "City":"Nonthaburi", "State":"THAILAND"}
]
}
}'
ตัวเช็ก ว่าเป็น Json หรือไม่
select isjson(@jsondata)
อ่านค่า
select json_value(@jsondata,'$.EmployeeInfo.Addresses[1].Address')
ดูว่าใช่หรือไม่
select isjson(@jsondata)
ดูค่า
select json_value(@jsondata,'$.EmployeeInfo.Addresses[1].Address')
select JSON_query(@jsondata,'$.EmployeeInfo.Addresses')
แก้
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Name'')
select json_value(@jsondata,'$.EmployeeInfo.FirstName')
เพิ่ม
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName', 'NNK')
select JSON_value(@jsondata,'$.EmployeeInfo.MiddleName')
select * from openjson(@jsondata)
Select JSON_query(@jsondata,'$.EmployeeInfo.Addresses')
แก้ ค่า
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Rakesh')
select json_value(@jsondata,'$.EmployeeInfo.FirstName')
เพิ่ม field
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName', 'MAI')
select JSON_value(@jsondata,'$.EmployeeInfo.MiddleName')
select * from openjson(@jsondata)
การ Update จะมา เป็น JSON ต้อง แตกออก โดยใช้ OPENJSON
DECLARE @v AS NVARCHAR(4000)
set @v='{"UserID":7,"UserName":"Somsak","RegDate":"2017-01-23T00:00:00"}'
ถ้า select * from OPENJSON(@v)
จะได้
key . value . type
UserId 7 2
UserName Somsak 1
RegDate 2017-01-23T00:00:00 1
บาง ค่า เราใช้ Where ได้เลย
select * from OPENJSON(@v) with ( UserID int '$.UserID', UserName varchar(50) '$.UserName', RegDate datetime '$.RegDate')
ผล
UserId UserName RegDate
7 Somsak 2017-01-23 00:00:00.000
แล้วจึงใช้ Insert หรือ Update ได้ แทน @v ลงใน
$$JSON$$
string cmdtext = "INSERT INTO [temp_Users] ([UserID],[UserName],[RegDate])"+
"Select UserID,UserName,RegDate from OPENJSON('$$JSON$$')with ( UserID int '$.UserID', UserName varchar(50) '$.UserName', RegDate datetime '$.RegDate')";
ในกรณีที่ได้ ผลจาก query มาแล้วต้องการเขียนเป็นโปรแกรมแบบ Class (no sql) ก็ทำได้โดยเข้า Web
json2charp.com และ เลือก Quicktype (ใน Visual Studio ต้องลง Nuget ของ Newtonsoft ก่อน)
เช่น ผลจาก Query
[{"UserID":1,"UserName":"Phar","RegDate":"2017-01-23T00:00:00"},{"UserID":2,"UserName":"Som"},{"UserID":3,"UserName":"Cho"}]
Generate จาก json2charp.com จะได้ Class .ให้ไปเปลี่ยน
Welcome Class เอา
namespace QuickType
{
using System;
using System.Collections.Generic;
using System.Globalization;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
public partial class
Welcome
{
[JsonProperty("UserID")]
public long UserId { get; set; }
[JsonProperty("UserName")]
public string UserName { get; set; }
[JsonProperty("RegDate", NullValueHandling = NullValueHandling.Ignore)]
public DateTimeOffset? RegDate { get; set; }
}
public partial class
Welcome
{
public static Welcome[] FromJson(string json) => JsonConvert.DeserializeObject<Welcome[]>(json, QuickType.Converter.Settings);
}
public static class Serialize
{
public static string ToJson(this Welcome[] self) => JsonConvert.SerializeObject(self, QuickType.Converter.Settings);
}
internal static class Converter
{
public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
{
MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
DateParseHandling = DateParseHandling.None,
Converters =
{
new IsoDateTimeConverter { DateTimeStyles = DateTimeStyles.AssumeUniversal }
},
};
}
}
เวลาใช้ใน SQL c# จะเป็นแบบนี้
string connString = @"Data Source=127.0.0.1\sql_server_demo;User ID=sa;Password=???;Initial Catalog=MyDb;Integrated Security=";
string cmdtext = "SELECT * FROM [temp_Users] FOR JSON AUTO";
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdtext, conn))
{
SqlDataReader rdr= cmd.ExecuteReader();
// Get the values
while (rdr.Read())
{
//listBox1.Items.Add(rdr[0]);
textBox1.Text =rdr[0].ToString();
QuickType.Welcome[] datas= QuickType.Welcome.FromJson(textBox1.Text);
string username = datas[0].UserName;
}
}
}
}
ลดเวลาในการเขียนและ Validate โปรแกรมได้มาก
