How to extract JSON object inside SQL Server

Question

I came across issue on extracting boolean or integer value from SQL Server and can't find any reference to it. Most of the examples or talks I came across are parsing JSON object and extract back as JSON. Maybe I didn’t understand it well, and does not understand SQL Server does casting SQL table into JSON in the background.

Recently I started to work on SQL Server, and I was ask to return data as JSON object. One of columns holds frequent change value and it can be an integer or boolean and the type for this column is NVARCHAR. We thought that when we extract it and convert to JSON by using SQL Server JSON AUTO key word and we would get our raw data back. But that was not the case, we get string back.

We try to store the data as JSON object

Insert in db_table(id, info) value (1, N’{a:true}’)

When we extract it

Select * from db_table ... JSON AUTO

Then we would get our JSON back

[{ id: 1, info: { a: true }}]

Instead we get { a: true } for info.

When we extract data with JSON, does it treat every value inside the table as string? How can I extract the original data?

what language of application are you using to retrieve the information from sql server? node? django? c#? thanks — arcee123
– arcee123, Commented Apr 16, 2020 at 22:43
also, you are inserting a string. if you want to use JSON AUTO, why not have a table using boolean? — arcee123
– arcee123, Commented Apr 16, 2020 at 22:45
@arcee123 thanks for your comments, I am using node. I am inserting string? I thought N'' retain info that I passing in? how do i insert a boolean or integer in there? — roger
– roger, Commented Apr 16, 2020 at 22:54

Dale K · Accepted Answer · 2020-04-16 23:16:27Z

2

JSON objects in SQL are stored as strings. The concept of a document or JSON object doesn't really exist in SQL. The built in parsing logic is relatively new as well. Since you are storing the entire JSON object in a single column, rather than parsing out the values into individual columns, you have to select it as a JSON_QUERY upon returning it.

Also, in your example your JSON is invalid. the 'a' key needs to be properly quoted.

DECLARE @db_table TABLE (id int, info NVARCHAR(MAX))

INSERT INTO @db_table
VALUES
(1, N'{"a":true}')

SELECT id, JSON_QUERY(info) as info from @db_table FOR JSON AUTO

edited Apr 16, 2020 at 23:16

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Apr 16, 2020 at 22:59

Aaron Hughes

4763 silver badges6 bronze badges

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

Collectives™ on Stack Overflow

How to extract JSON object inside SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related