Get Value From Json object contain table column using SQL Query

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.

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.NameFROM 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.CategoryNameFROM 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.BuildingFROM Books b    CROSS APPLY OPENJSON (b.BookCategory) WITH    (        CategoryId INT '$.Id',        Category VARCHAR(100),        CategoryName VARCHAR(100) '$.Name',        Building VARCHAR(100) '$.Location.Building'    ) c