The OPENJSON return schema

Question

Post reply

The OPENJSON return schema

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
September 24, 2021 at 12:00 am

#3927252

Comments posted to this topic are about the item The OPENJSON return schema

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

edwardwill SSCertifiable Points: 5851 More actions · Answer 1

I've not used this functionality, but the spec. doesn't say to me what the topic suggests:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15#with_clause

Jason- SSCrazy Points: 2795 More actions · Answer 2

This comment alludes to the expected correct answer so I put it in a spoiler block.

Spoiler:

Either I misunderstood the question (which is likely here) or the below working code should not work since I didn't specify a path in the WITH clause, what am I misunderstanding?

DECLARE @j_data NVARCHAR(max) = '[
{
"row_id": 1, 
"row_text_value": "This is my text", 
"row_date_value": "2021-09-21T08:31:48"
}, {
"row_id": 2, 
"row_text_value": "This is my other text", 
"row_date_value": "2021-08-07T18:29:13"
}
]'

SELECT * FROM openjson(@j_data) WITH (
row_id INT, 
row_text_value VARCHAR(128), 
row_date_value DATETIME2
)

The documentation reads:

with_clause contains a list of columns with their types for OPENJSON to return. By default, OPENJSON matches keys in jsonExpression with the column names in with_clause (in this case, matches keys implies that it is case sensitive). If a column name does not match a key name, you can provide an optional column_path, which is a JSON Path Expression that references a key within the jsonExpression

-

edwardwill SSCertifiable Points: 5851 More actions · Answer 3

Spoiler:

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 4

Good catch. I misread the help. I've updated the answers and explanation.