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
-1
votes
1
answer
57
views
How can I modify JSON object from a column while retrieving the data in SQL Server
I have a JSON array object like below in a SQL Server table column:
[
{
"Name": "test1",
"Favouties": {
"LikedColor": "Red",
&...
- 41
1
vote
1
answer
256
views
Using OPENJSON in SQL Server to parse a Non-Array Object
I'm using SQL Server v15, called from a .NET application.
A website I'm using (not mine - I don't control the data) has a JSON dataset formatted strangely. Instead of being an array like:
[{"id&...
- 3,723
0
votes
1
answer
223
views
SQL Server update JSON with url in a property without introducing escaping sequences
I am trying to update an array in a JSON contained into a column inside my DB, it's not clear to me what i did wrong but seems like that when a JSON property (a string) contain backslash, those value ...
- 1,352
0
votes
2
answers
158
views
Parse Google API JSON file to rows and columns with OPENJSON in T-SQL
So I am trying to create a query than can handle a json file that we get with a data factory web request from the Google Analytics API 4 and store the result in an Azure sql table. The following query ...
0
votes
1
answer
140
views
OPENJSON select value by dynamic key
data row 1 :
{
"30":{"status":0,"approval":"0","entrydate":"2023-01-30"},
"26":{"status":0,"approval":...
1
vote
1
answer
70
views
Parse JSON Column containing multiple arrays
I'm trying to get this result but I'm not getting it. I've searched the forum but I can't find a solution to my problem. Can you help me and explain what is wrong with my query?
thanks.
JSON
{
"...
- 39
0
votes
1
answer
200
views
SQL: The multi-part identifier could not be bound with OPENJSON
I'm trying get objects from JSON by this query
SELECT
co.contract_number
, co.objectId id1
, cbs.id id2
, co.summary
FROM (
SELECT
c.contract_number
, cb.summary
...
1
vote
2
answers
961
views
Use openjson to get columns from JSON with multiple arrays of elements
JSON input looks like this:
{
"reporting.unit": [ "F-1", "F-2", "F-3"],
"notional.lc": [ 100.1, 140.2, 150.3]
}
Desired Output:
reporting.unit
...
- 23
2
votes
1
answer
74
views
SQL Server parse nested json using OPENJSON
I am trying to read JSON that I would like to parse each collection in SQL Server.
The structure is as follows Root > Action > GoActionFiles / Pools
I would like to return a row for each ...
- 979
1
vote
0
answers
180
views
SQL Server OPENJSON read nested JSON same key array
I am following the idea from Ed.Schavelev on article 'SQL Server OPENJSON read nested json'. But I need instead of repeating the rows I wanted them (4) to be in the columns. Is this possible?
I have ...
- 51
0
votes
0
answers
739
views
Add EF Core 6 OPENJSON translation which passes the column name instead of string
I am working on a legacy project which uses SQL Server and stores JSON is some of the columns, which has been upgraded to EF Core 6.
My main issue is that I have an old query which probably ran client-...
- 50.9k
0
votes
1
answer
847
views
How to use OPENJSON to concatenate json Array in a column of a table
I have a column in SQL table which has a string value denoting a JSON array. I need to concatenate the array for comparison. There are multiple rows of this data and will be using a CURSOR to iterate ...
- 673
0
votes
1
answer
676
views
Maximum 5000 rows returned using OPENJSON in SQL Server?
Using Azure Data Factory I have created a pipeline to upload any number of JSON files from Azure blob storage. I am loading the JSON data into a stage table with the following fields
FileName varchar(...
- 1
1
vote
1
answer
467
views
SQL Server JSON: error converting data type nvarchar to decimal... sometimes
I'm having a weird problem. I have the following SQL statement trying to parse a JSON file:
SELECT
A.subscription_id AS subscriptionid,
A.customer_id AS customerid,
A....
0
votes
1
answer
127
views
SQL Server reduce recurring XML nodes to JSON array
I have some XML in which every entry can contain some recurring elements. I'm trying to query it with OpenXML function and I want to reduce those elements to JSON arrays.
My SQL looks like this:
...
- 187