ต้อง Set database ทีทำงานให้ support ก่อน
ALTER DATABASE MYDB SET COMPATIBILITY_LEVEL = 130
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[CustomerName] [nvarchar](50) NULL,
[OrderDetails] [nvarchar](max) NULL)
INSERT INTO Orders (OrderID, CustomerName, OrderDetails)
VALUES (1, 'John test',
'{"items": [{"name": "Widget", "quantity": 2},
{"name": "Gizmo", "quantity": 4}],
"total": 99.99}')
INSERT INTO Orders (OrderID, CustomerName, OrderDetails)
VALUES (2, 'betty ok',
'{"items": [{"name": "Pen", "quantity": 3},
{"name": "Gizmo", "quantity": 2}],
"total": 88}')
ทดสอบ query
SELECT *
FROM Orders
WHERE EXISTS (
SELECT *
FROM OPENJSON(OrderDetails, '$.items')
WHERE JSON_VALUE(value, '$.name') = 'Widget'
)
ไม่มีความคิดเห็น:
แสดงความคิดเห็น