Questions tagged [open-json]
OpenJson is a T-SQL (SQL Server) built in table valued function, introduced in 2016 version. Use this tag with questions related to this command.
open-json
204
questions
70
votes
3
answers
102k
views
SQL Server OPENJSON read nested json
I have some json that I would like to parse in SQL Server 2016. There is a hierarchy structure of Projects->Structures->Properties. I would like to write a query that parses the whole hierarchy but ...
- 2,423
55
votes
4
answers
114k
views
SQL Server Invalid version: 15 (Microsoft.SqlServer.Smo)
Context: I'm having difficulty modifying a stored procedure in SQL Server 2016. The stored procedure performs parsing of json data within a file. For some reason I'm able to execute the stored ...
- 1,653
9
votes
1
answer
8k
views
How to use OPENJSON on multiple rows
I have a temp table with multiple rows in it and each row has a column called Categories; which contains a very simple json array of ids for categories in a different table.
A few example rows of ...
- 1,495
7
votes
1
answer
7k
views
I am trying to connect to abfss directly(without mounting to DBFS) and trying to open json file using open() in databricks
I am trying to connect to abfss directly(without mounting to DBFS) and trying to open json file using open() method in databricks.
json_file = open("abfss://@.dfs.core.windows.net/test.json') ...
- 71
6
votes
2
answers
7k
views
How to get the detail row number for rows in JSON string
I have a stored procedure that accepts a NVARCHAR(max) string that is JSON data that I need to validate before loading it into the live tables. If the validation fails I need to return a message with ...
- 135
5
votes
1
answer
6k
views
Select results from multiple array elements in Json document with Sql Server 2016's OPENJSON function
Is it possible to combine parts of a json document from multiple array elements into a single result in Sql Server 2016?
Given this json:
{
"fruit": {
"types": [
{
"possible": [ "...
- 445
5
votes
1
answer
2k
views
OPENJSON collation in Azure Synapse causes a collation conflict error
I have an OPENJSON command that takes the parsed JSON and LEFT joins it onto an existing table.
When I add the LEFT JOIN I get the error:
collation conflict between "Latin1_General_BIN2" ...
- 3,363
5
votes
3
answers
4k
views
How to Set OPENJSON Path to Nested Array
I'm trying to set the path for my OPENJSON function for the nested array, but it's not working. Tried different variations and examples/resources I found online and still cannot figure it out.
Any ...
- 1,265
4
votes
1
answer
3k
views
How do I get the "key" of an array item in OPENJSON, when using WITH?
I want to parse a json-array in my sql-server. I would like to use OPENJSON with WITH to parse specific values into columns. How can I get the index of each array item?
I know, that this works fine ...
- 21.3k
3
votes
1
answer
5k
views
SQL OPENJSON - Parse JSON Array
I am hoping somebody can help point me in the right direction here regarding parsing an array object out of the following JSON example. Currently, I am able to parse out the majority of the json ...
3
votes
2
answers
1k
views
Accessing OPENJSON parsed JSON multiple times in a stored procedure?
Consider this JSON:
{
"Name": "Alice",
"Relations": [
{
"RelationId": 1,
"Value": "one"
},
...
- 16.7k
2
votes
2
answers
2k
views
Open Json a Json field in SQL Server to table [closed]
I have a table in SQL Server where a row contains a Json column - something like this:
ResponseText
RequestId
{"LosUnqCod":0,"LosMidId":23}
96173722
{"LosUnqCod":1,&...
- 358
2
votes
2
answers
5k
views
T-SQL OPENJSON WITH clause
This question is based on information from the JSON data in SQL Server article from the online documentaion:
DECLARE @json NVARCHAR(MAX);
SET @json = N'[
{"id": 2, "info": {"...
- 71
2
votes
1
answer
1k
views
SQL Server OPENJSON returns 1900-01-01 for date type when empty string is passed
When parsing JSON string using OPENJSON in SQL Server, if date field is empty, SQL returns 1900-01-01.
DECLARE @dt [date]
SELECT @dt=dt
FROM OPENJSON('{"dt":""}')
WITH (dt [date] '$.dt')
PRINT @dt
...
- 789
2
votes
2
answers
82
views
JSON Object Query SQL Server
I have a JSON string which is the following:
[
{
"id": 103001058774,
"name": "status",
"label": "Status",
"...
- 35