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
1
vote
1
answer
269
views
Add a column to the result of an OPENJSON query
DECLARE @json NVARCHAR(4000) = N'{
"pets" : {
"cats" : [
{ "id" : 1, "name" : "Fluffy", "sex" : &...
- 12.1k
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 ...
- 7,439
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 ...
- 25.9k
-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"
}, {
"...
- 11.9k
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-...
- 113k
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&...
- 147k
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), ...
- 45.2k
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&...
- 22.7k
-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": &...
- 10.3k
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 ...
- 748k