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.

Dmitry · Accepted Answer · 2017-09-28 20:30:56Z

46

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.

answered Sep 28, 2017 at 20:30

Dmitry

7,29614 gold badges42 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gopi Over a year ago

@Dmity, I need to display Name and Category as empty if BookCategory is invalid json or empty. How do I do that? I tried the below query but there is some mismatch in count SELECT Id, JSON_VALUE(ProgressJson,'$.ActualTIme') AS actualtime, JSON_VALUE(ProgressJson,'$.CurrentTIme') AS currenttime FROM [ProgressInstances] pi WHERE ProgressJson = CASE WHEN ISJSON(pi.ProgressJson) > 0 THEN ProgressJson ELSE '{}' END

Tigran Petrosyan Over a year ago

@Dmitry, using many JSON_VALUE-s in query harm performance or not ? for example If json in column have many parameters, will It harm performance or not to write JSON_VALUE for each parameter ? Thanks in advance. Maybe there is serialization for each json_value query ?

Dana · Accepted Answer · 2020-10-14 13:25:05Z

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

Marcello Miorelli · Accepted Answer · 2024-07-04 19:30:47Z

--To fetch the value of any key from a Json object in sql server --Here in the below query: OrderNumber is my Key for which i need to extract the value and sq.JSON is my Column name from sq table.

Query:


Select 
Replace(SUBSTRING(sq.JSON,                      
            CHARINDEX('"OrderNumber":', sq.JSON) + LEN('"OrderNumber":') + 1,                       
            CHARINDEX('"', sq.JSON, CHARINDEX('"OrderNumber":', sq.JSON) + LEN('"OrderNumber":')) -                     
            (CHARINDEX('"OrderNumber":', sq.JSON) + LEN('"OrderNumber":')+1)+                       
            CHARINDEX('"', sq.JSON, CHARINDEX('"OrderNumber":', sq.JSON) + LEN('"OrderNumber":') + 2) -             
            (CHARINDEX('"OrderNumber":', sq.JSON) + LEN('"OrderNumber":'))          
           ),'"','') as 'CustomerOrderNumber' from ServiceOrder sq

Collectives™ on Stack Overflow

Get Value From Json object contain table column using SQL Query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related