1

I have a table that has some columns. One of these columns stores data in JSON format. I select a row from this table with FOR JSON AUTO. My problem is that SQL Server puts quotations around the value of JSON properties but I don't want this; because I want to use the values of inner JSON with JSON_VALUE(). What can I do?

Code:

SELECT TOP 1 *
FROM users;

Result:

name    lastName    age    favorites
John    Duo         20     {"city": "paris", "color": "blue", "sport": "football"}

Code:

SELECT TOP 1 *
FROM users
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

Result:

{"name":"John","lastName":"Duo","age":20,"favorites":"{\"city\": \"paris\", \"color\": \"blue\", \"sport\": \"football\"}"}

Code:

SELECT JSON_VALUE(@user_json,'$.favorites.color')

Result:

NULL

I can use this trick to get values from inner JSON, but it's not clean.

Code:

SELECT JSON_VALUE(JSON_VALUE(@user_json,'$.favorites'), '$.color')

Result:

blue

How can I do this in a clean way?

Some code for testing in SQL Server:

DROP TABLE IF EXISTS #test_tbl;
DECLARE @user_json AS NVARCHAR(MAX);
  
SELECT 'John' AS Name, 'Duo' AS LastName, 20 AS Age, '{"city": "paris", "color": "blue", "sport": "football"}' AS favorites
INTO #test_tbl;

SET @user_json =
(
    SELECT *
    FROM #test_tbl
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)

SELECT JSON_VALUE(@user_json,'$.favorites.color');

SELECT JSON_VALUE(JSON_VALUE(@user_json,'$.favorites'),'$.color');
Zhorov
  • 28,486
  • 6
  • 27
  • 52
MohammadHosein Masoon
  • 13
  • 2

1 Answers1

0

You need to nest the favorites JSON using json_query(), e.g.:

SET @user_json =
(
    SELECT Name, LastName, Age, json_query(favorites) as favorites
    FROM #test_tbl
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)

SELECT JSON_VALUE(@user_json,'$.favorites.color');

# (No column name)
# ----------------
# blue
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • My columns are in change and I want to use * sign to have all columns in my JSON. – MohammadHosein Masoon May 10 '21 at 04:48
  • 1
    You can't do that @MohammadHoseinMasoon . SQL queries need to be well defined *before* they are run, not defined as they run. *You* need to define what columns you want in your dataset; there is no magic "SELECT {any column without defined structure} FROM...` syntax. – Thom A May 10 '21 at 07:05