Stack Overflow
Stack Overflow

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.

OpenJson was introduced in SQL Server 2016.
It takes in a nvarchar containing JSON data, with an optional nvarchar string containing a JSON Path Expression, and an optional With clause.

When the With clause is not supplied, OpenJson will return a table containing three columns:
Key (nvarchar(4000)), Value(nvarchar(max)) and Type (int).
values of the Type columns indicates the following:

Type    JSON data type
0       null
1       string
2       int
3       true/false
4       array
5       object

The With clause allows the user to explicitly define the columns of the table returned.
The With clause contains three parameters for each value to get from the JSON:
Column name, data type, and an optional JSON Path expression (if not supplied, OPENJSON will try to get a property with the same name as the target column.

If the json property extracted contains an inner json object, the AS JSON option can be specified with this column, to enable further JSON processing.

Articles

197 questions
2
votes
1 answer

How to Update and Insert Array of Objects in table with OPENJSON and where conditions

I want to update and insert the stock, InvM, and Invoice table with OPENJSON(). I am new at OPENJSON() in SQL Server. I have an array of objects and I want to insert each object to new row of the tables. I want to iterate through every object and…
Syed Muhammad Moiz
  • 385
  • 2
  • 16
2
votes
2 answers

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": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info":…
TPV
  • 61
  • 1
  • 5
2
votes
1 answer

Convert JSON to comma-separated list in SQL Server with OPENJSON

In SQL Server 2016, I am trying to convert the following JSON: DECLARE @json NVARCHAR(MAX); SET @json = N' { "date": "2021-12-31", "distributor_name": "Test", "movies": [ { "category_id": 3, …
MiniG34
  • 312
  • 2
  • 12
2
votes
1 answer

Access JSON_VALUE from SQL Column containing JSON String with Arrays

Looking to use JSON_VALUE to access some data within a JSON column in an SQL Server table. I have a table (tblMyTable) that has the following columns and data; | Column1 | JSONColumn | | -------- | -------------- | | 1 | {some JSON} …
CapnComic
  • 23
  • 3
2
votes
2 answers

Open Json a Json field in SQL Server to table

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,"LosMidId":5} 96173721 I want to have a table in this…
fahime abouhamze
  • 328
  • 3
  • 16
2
votes
1 answer

How to get complex JSON string into columns SQL Server

I have stored some data in the table as JSON string like below. [ { "firstName":"John", "lastName":"Smith", "age":25, "Address":{ "streetAddress":"21 2nd Street", "city":"New York", …
user2837480
  • 349
  • 2
  • 18
2
votes
1 answer

When querying using OPENJSON, will the result set mimic the order of the JSON array or will the order be random?

Let's say I have this JSON array: [{ "Field": "1" }, { "Field": "2" }, { "Field": "3" }] And I use that in an OpenJSON query in SQL Server. Will the query result set be always the same order as the array: Field 1 2 3 Or will it be…
Kevin Ganancial
  • 37
  • 7
2
votes
1 answer

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 correctly. Where I am getting stuck is pulling out the…
Jeremy Miedreich
  • 23
  • 1
  • 3
2
votes
1 answer

OPENJSON Convert Value Column to Multiple Rows does not work

I have a JSON file with a simple structure. I try to extract data into rows out of this JSON File. The JSON File starts with: [{"result": [{"country":"Germany", "parent":"xxxx", "city":"Reitbrook", "latitude":"", I tried this code, all…
Nico 't Hoen
  • 21
  • 1
2
votes
1 answer

A concatenation OPENJSON in a SQL Server 2016 stored procedure

I need to combine all of the authors for a particular UID. The basic fields are working from the code at another post. DECLARE @json NVARCHAR(MAX) SET @json = '{ "header": { "type": "esummary", "version": "0.3" …
Bill
  • 1,423
  • 2
  • 27
  • 51
2
votes
3 answers

OPENJSON - Unable to query nested elements

I'm having difficulty querying the nested elements of a JSON document using the T-SQL OPENJSON function in SQL Server 2016. I read through the MSDN docs here , here & here yet havent found the correct syntax for the WITH block. My columns keep…
emalcolmb
  • 1,585
  • 4
  • 18
  • 43
2
votes
2 answers

Using TSQL OPENJSON how do I extract a value from JSON array with a dynamic key name

I have a json document with an internal array of attributes. On one of these attributes, the key name changes dynamically/randomly. I can easily extract all the data points except for this last pesky attribute. All the methods I have found or used…
K_OFLYNN
  • 35
  • 3
2
votes
2 answers

How to parse JSON string recursively with openjson

I have the following JSON data : set @json = N'{ "Book":{ "IssueDate":"02-15-2019" , "Detail":{ "Type":"Any Type" , "Author":{ "Name":"Annie" , "Sex":"Female" } …
Annie
  • 139
  • 14
2
votes
1 answer

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 Output: 1900-01-01 How can I prevent this from…
Prashant Agarwal
  • 729
  • 7
  • 23
2
votes
1 answer

SQL Server JSON Array

I have some JSON that I would like to parse in SQL Server 2016. There is a hierarchy structure with arrays. I would like to write a query that parses the whole hierarchy more efficiently, I have challenges trying to access embedded arrays especially…
okkadu
  • 45
  • 7
1
2
3
…
13 14