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