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
0
votes
1
answer
696
views
OPENJSON SQL call to API with record pagination
I'm trying to consume an API, parse the data and then put it in a table. I've done several tests and I can't have all the records because the API has pagination.I've seen other posts and I can't find ...
- 39
1
vote
1
answer
597
views
Add data type to JSON values query
I am building the following view on SQL Server. The data is extracted from Soap API via Data Factory and stored in SQL table. I union two pieces of the same code because I am getting outputs from API ...
- 27
0
votes
1
answer
199
views
OPENJSON does not detect duplicated Value
I need to import and parse some sales records into the database.
Some customers have more than one product, therefore there are several orders with different product but with the same shipping address ...
- 1,045
2
votes
1
answer
639
views
Using SQL Server Indexed View in combination with OPENJSON
I have a table with just one row and one column which stores a JSON array with about 30MB/16k objects in it:
CREATE TABLE [dbo].[CitiesTable]
(
[CitiesJson] [NVARCHAR](MAX) NOT NULL
) ON [PRIMARY]...
- 5,990
1
vote
1
answer
3k
views
SQL Parse Json array to rows
I'm trying to parse the 'custinfo' array to rows, rather than specific columns how I have in my query (there can be none or many values in the array)
DECLARE @json NVARCHAR(MAX) ='{
"...
- 303
2
votes
1
answer
1k
views
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 ...
0
votes
0
answers
98
views
OPENJSON unable to parse Portuguese characters
I'm trying to convert my JSON data into a table format in SQL Server Express 2019. Following are my JSON data:
DECLARE @token INT;
DECLARE @ret INT;
DECLARE @url NVARCHAR(MAX);
...
- 39
1
vote
1
answer
5k
views
Using OPENJSON in SQL Server Query
I'm trying to pull some information from a JSON array stored in an SQL Server database. I can see I need to use OPENJSON, and I've found numerous examples of how to use it, but the rub is all of the ...
- 99
1
vote
1
answer
86
views
SQL on US Census - Null values return
I am trying to get data from the US Census on SQL Server Studio for analysis (trade data at port level). I have downloaded a JSON file for now from their API (ideally, I will do a call from SQL studio ...
- 13
1
vote
1
answer
386
views
How Can I use OpenJson to access double nested arrays IN T-SQL?
I have a JSON file listed below and I am having issues using OPENJSON to get access to the Misc field when the CheckboxValue is true.
So the example below would return a table:
Company ...
- 57
0
votes
1
answer
134
views
How to use OPENJSON if column is same in SQL?
My database table is created as :
declare @library table (dependencies varchar(max))
insert into @library values('{"jar":"azure.com","maven":"azure.com","...
- 127
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
1
vote
1
answer
233
views
OpenJson to parse and partially update a json column column
I have this table:
CREATE TABLE [dbo].[Device]
(
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[DeviceStatus] [int] NOT NULL,
[Type] [nvarchar](64) NOT NULL,
[Serial] [nvarchar](64) NOT NULL,
...
- 2,053
1
vote
1
answer
147
views
OPENJSON() optimization for poorly structured API response
I'm trying to use the TSheets API to pull data into my internal database, but the responses come back in a way that I can't figure out how to efficiently get it into table structure.
I've simplified ...
- 808
0
votes
1
answer
956
views
Json to table without explicit key names
I have a table with an VARCHAR(MAX) column which stores JSON key value pairs.
The JSON document schema is simply a different number of key value pairs, no nesting, nor arrays in there.
I wish to build ...
- 1,136