Get Value From Json object contain table column using SQL Query

Question

I have a table Books with two columns Id, BookCategory in a SQL Server database, where Id is the primary key and BookCategory contains JSON object.

This my table Books

I'm trying to get table BookCategory column value as Category only. For that, I wrote the following SQL query but I didn't get. any have an idea about to get JSON object value as the normal column value as a query result.

SELECT 
    Id, BookCategory.Name AS Name, 
    BookCategory.Category AS Category 
FROM
    Books

So the result will look like :

Id=1
Name=CA
Category=cs

And I'm getting an error in SQL Server Management Studio:

The multi-part identifier "BookCategory.Name" could not be bound.

Any help to get result same of the above table Books.

Thanks in advance.

user_2003070 · Answer

It should be

SELECT 
    Id, 
    JSON_VALUE(BookCategory,'$.Name') AS Name, 
    JSON_VALUE(BookCategory,'$.Category') AS Category 
FROM
    Books

Docs: https://docs.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.

user_3038395 · Answer

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

Get Value From Json object contain table column using SQL Query

2 Answers