Get Value From Json object contain table column using SQL Query
You can select multiple values from a json column with CROSS APPLY
.
SELECT
BookId = b.Id,
BookTitle = b.Title,
CategoryId = c.Id,
c.Category,
CategoryName = c.Name
FROM Books b
CROSS APPLY OPENJSON (b.BookCategory) WITH -- BookCategory is the json column
(
Id INT,
Category VARCHAR(100),
[Name] VARCHAR(100)
) c
If you like, you can rename columns selected during the CROSS APPLY
. This has the same result as above.
SELECT
BookId = b.Id,
BookTitle = b.Title,
c.CategoryId,
c.Category,
c.CategoryName
FROM Books b
CROSS APPLY OPENJSON (b.BookCategory) WITH
(
CategoryId INT '$.Id',
Category VARCHAR(100),
CategoryName VARCHAR(100) '$.Name'
) c
You can also select nested properties from objects in a CROSS APPLY
. Suppose your BookCategory
also included a Location:
{ Id: 1, Name: CA, Category: cs, Location: { Building: Main, Floor: 2, Shelf: 4F }}
You can include Building like:
SELECT
BookId = b.Id,
BookTitle = b.Title,
c.CategoryId,
c.Category,
c.CategoryName,
c.Building
FROM Books b
CROSS APPLY OPENJSON (b.BookCategory) WITH
(
CategoryId INT '$.Id',
Category VARCHAR(100),
CategoryName VARCHAR(100) '$.Name',
Building VARCHAR(100) '$.Location.Building'
) c
It should be
SELECT
Id,
JSON_VALUE(BookCategory,'$.Name') AS Name,
JSON_VALUE(BookCategory,'$.Category') AS Category
FROM
Books
Docs: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
You need to use special functions to work with JSON in SQL (JSON is supported in MS-SQL 2016). Table.column
notation is for regular SQL column types like INTEGER
or VARCHAR
etc.