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.