use Json auto into open Json in SQL

Question

Is this syntax possible in SQL:

     SELECT * 
     FROM OPENJSON(SELECT * FROM FoodSara_tbl FOR JSON AUTO)

If yes, can you explain me how and why?

If no, Why? and what is the beast way instead of that?

question from:https://stackoverflow.com/questions/65650485/use-json-auto-into-open-json-in-sql

Answer 1

this query work correct !!!

SELECT * 
FROM OPENJSON(CONVERT(NVARCHAR(MAX),(SELECT * FROM [dbo].[temp1] FOR JSON AUTO)))

OPENJSON command give a string contain a json data as parameter but when you generate json from table you have a pure json as result Set and OPENJSON give a string parameter as json.

The two are different

if you run this code

SELECT * FROM [dbo].[temp1] FOR JSON AUTO

you see this result

[{"Pname":"Ava","Pregion":"German","Pcount":10},{"Pname":"Ava","Pregion":"UK","Pcount":5}]

if put this result on OPENJSON

SELECT * 
FROM OPENJSON([{"Pname":"Ava","Pregion":"German","Pcount":10},{"Pname":"Ava","Pregion":"UK","Pcount":5}])

see below error

Invalid column name '{"Pname":"Ava","Pregion":"German","Pcount":10},{"Pname":"Ava","Pregion":"UK","Pcount":5}'.

but if you add ' at the first and foremost of your json. its parse correct

' is sign of string in SQL Server