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
2
answers
66
views
Assistance with TSQL OPENJSON extracting metadata and data into table
I have this JSON:
{
"environments": [
{
"environment": "Development",
"customs": {
"FieldA": "...
- 41
2
votes
0
answers
84
views
Entity Framework Core 8 Where IN vs Where IN OPENJSON Additional Question
I have reviewed the other post on this, and found my scenario to be much more un workable, to the extent that I am unsure if it is the way I am writing the query that is the issue. Surely it must be ...
- 37
2
votes
1
answer
224
views
EF Core 8 - translate queries into embedded collections (OPENJSON) using expression
Here is an example of EF Core 8 translating LINQ to embedded collections:
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/whatsnew#translate-queries-into-embedded-collections
And it ...
- 23
-1
votes
1
answer
67
views
Using numeric field names in SQL JSON [duplicate]
I have the following code example:
Insert into dbo.Data (
COL_0
select
A_1
from OPENJSON (@jsonData)
with (
A_1 nvarchar(max) '$.0'
)
which triggers:
ex {System....
- 1,469
1
vote
2
answers
103
views
Parse nested JSON in SQL
I have this JSON:
[{
"id": 1,
"meta": [{
"key": "key1",
"value": "ValueKey1"
}, {
"...
- 3,779
0
votes
0
answers
34
views
Unable to Parse the Json file using Open Json in SQL
I've a JSON file content and I want read the content using Open JSON and load it into the SQL.
need help in loading the content to SQl.
{
"Header": {
"Time": "2023-...
0
votes
0
answers
21
views
JSON string in MS SQL Server is too large to select without CAST ing to XML [duplicate]
We have event data in a log table that is a JSON string of NVARCHAR(MAX) datatype. Some of the strings are too large to SELECT out of the table. But, if we CAST it, we can see the entire value.
SELECT ...
- 339
1
vote
1
answer
41
views
How to dynamically load SQL tables using OPENJSON
I have a json that looks like
[{
"year": 2021,
"state": "Nebraska",
"report": "Farm Business Balance Sheet&...
0
votes
1
answer
61
views
Get element with newest date
I'm trying to get the element with the newest date from various JSON arrays. But I can't get it to work. It returns all the elements from the arrays. I have tried several things with MAX, TOP(1), ...
- 79
0
votes
1
answer
24
views
Parse Data from OPENJSON()
Here is my string:
SELECT * FROM OPENJSON ('{"data":{"CAD":1.3480102032}}')
WITH ( Rate VARCHAR(200) '$.data')
I need to extract the value 1.3480102032
Sorry, new to this!
The ...
- 3
0
votes
1
answer
82
views
SELECT all fields from OPENJSON
SELECT all fields from OPENJSON
I have Json data below. How can I select all available fields from OpenJson? I am using SQL Server 2016.
DECLARE @JsonData varchar(max)
SET @JsonData =
'{"status&...
- 11
-2
votes
1
answer
61
views
How to read a value from a particular object in a list with JSON string using MS SQL [closed]
[{
"resourceType": "virtualMachines",
"name": "Standard_E16-4as_v5",
"tier": "Standard",
"size": &...
1
vote
1
answer
43
views
Using param in SQL stored procedure select value
I have a stored procedure that I am using to extract values from a JSON column in a table:
@ReferenceID int,
@BureausID int,
@EmployersID int,
@FileType varchar(12),
@TargetIndex ...
- 1,467
0
votes
2
answers
373
views
How to get @odata.etag value from JSON in SQL Server table
Does anybody knows how to get @odata.etag value from a JSON into a SQL Server table? Many thanks!
JSON string:
{
"@odata.context": "https://mycompany.com/Tenant/ODataV4/$metadata#...
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
-2
votes
1
answer
71
views
Python: difficulty in parsing file as JSON
I have uploaded a file here that I am trying to parse as JSON, but my code below is not working:
with open('all_links_dict.json') as fd:
json_data = json.load(fd)
print(json_data)
- 13
1
vote
1
answer
47
views
Extract head of an array using open_json?
I am have sample JSON as below
{
"12345": {
"percent": 26,
"avgPrice": 32,
"avgNewOfferCount": 12,
"avgUsedOfferCount&...
0
votes
1
answer
151
views
Optimal way to filter table rows based on a variable JSON array in SQL using JOIN or other methods?
I have a table A with a large number of rows and an id column. Additionally, I have a variable @Json that can have three types of values: null, an empty array '[]', or a JSON array like '[1,2,3]'. I ...
- 43
0
votes
1
answer
315
views
OPENJSON SQL Server Error: Invalid column name 'property-with-dash'
I am working in SQL Server and I Need to select "name-surname" tag from below mentioned JSON but getting following error:
JSON path is not properly formatted. Unexpected character '-' is ...
- 5
-1
votes
1
answer
138
views
Parse JSON from tsql
I need to parse JSON using TSQL.
I find a function to create a temporary table contains all values of the json and the mode to find every elements of it.
Not there are an issue. I have a JSON with an ...
- 2,113
1
vote
2
answers
135
views
SELECT * FROM OPENJSON -- Not getting expected values
I am getting 0 row when selecting from OPENJSON below. I am expecting to get all 3 values "AA" for different as_of_date for CREDIT_RTG . I tried different select statements. I am running in ...
- 11
1
vote
1
answer
45
views
Can I use dot navigation when using multiple parameters to filter (compare) in SQL SELECT?
I new in SQL, I want to retrieve only the records of a certain groupID and I am using this query:
SELECT @Date, Capacity, UserOrUserGroup
FROM UCTimePhaseMonthly
WHERE Date >= 2023-05-01
AND ...
- 703
0
votes
2
answers
82
views
Convert JSON value in single row in TSQL 2019
I need help in transforming a string to json to put the values in separated lines.
Using tsql (2019)
How do I have to transform the provided data (payload) into a json to create the expected result or ...
0
votes
2
answers
158
views
Get different values from JSON array
I have a SQL table containing 2 columns - first Column is the ID of the users and the second column holds the JSON Phone information. The phone information consist of Work, Home phones and whether it ...
- 79
0
votes
1
answer
113
views
Getting NULL with OPENJSON read JSON with SQL
I have JSON like below. and I want to find all models from the JSON.
When I give below SQL query it gives me only Sedan models
DECLARE @PermsJSON NVARCHAR(MAX) =<json>;
select * from OPENJSON(@...
0
votes
0
answers
251
views
SQL Server settings for OPENJSON
I have an instance of SQL Server running Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64)
In one database, if I use JSON functions I get the following error:
Invalid object name '...
- 99
2
votes
1
answer
2k
views
Explode/normalize a column with JSON stored as a string into rows and columns [duplicate]
I am using SQL Server and have a column that has JSON stored as a string in it. I am trying to explode/normalize the JSON into new rows and columns. Below is a picture of how the table currently looks....
- 31
0
votes
0
answers
104
views
Default value for column is not set at moment of insert using openjson
Consider the following statement to create a table, note the default value for MyDateTime:
USE [MY_DATABASE]
GO
ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [DF_MyTable_MyDateTime]
...
0
votes
1
answer
99
views
OPENJSON - parsing nested JSON preserving relations
I have a JSON array that looks like this
[
{
"_id": "12345",
"uniqueId": null,
"companyName": "ABC Corp",
"yearFounded": ...
- 445
0
votes
1
answer
56
views
Json input to Sql table
I've a requirement to load the json input into a table which has multiple arrays
declare @json nvarchar(max);
set @json =
'{
"method":"email",
"value&...
- 21
-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
255
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
466
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
2
votes
2
answers
208
views
SQL Server 2019 - Update Table by JSON Array of object
I have a table like this:
CREATE TABLE WeeklySlots
([dow] int, [slots] int, [SlotCode] varchar(6))
;
INSERT INTO WeeklySlots
([dow], [slots], [slotCode])
VALUES
(1, 0, 'T19_00'),
(...
- 1,045
0
votes
2
answers
559
views
Parsing json with SQL Server OpenJson
I am trying to parse the following json:
declare @json nvarchar(2048) = N'{
"brand": "BMW",
"year": 2019,
"price": 1234.6,
"colors": ["red",&...
- 1,585
2
votes
1
answer
384
views
sql-server OPENJSON ways to parse multiple array value elements as one element?
Is there is any other (more pretty) way to accomplish the following?
I am struggling with a way to display multiple elements of an array as one element.
I have a JSON-string, that looks like this:
...
- 23
0
votes
0
answers
369
views
JSON text is not properly formatted. Unexpected character '1' is found at position 0
i am getting this error in ssms while using openjson function
JSON text is not properly formatted. Unexpected character '1' is found at position 0.
Below is my code.
DECLARE @json NVARCHAR(MAX)
...
- 85
-1
votes
1
answer
90
views
OPENJSON() returns 0 rows from SQLCMD, but returns many rows in SSMS
As the title says, I have a set of SQL queries using OPENJSON() that work beautifully in SQL Server Management Studio, but when ran from the Command Prompt using SQLCMD, the queries return 0 rows.
...
