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
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.
...
1
vote
1
answer
108
views
Stored procedure with multiple insert
I want a value of #temp table and People table value together. I can use json_modify but it will work only for single value insert. But in case of multiple values, we can make both in JSON object ...
- 11
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" : &...
- 334
0
votes
1
answer
254
views
How to fetch the json column data from database in SQL Server?
I have created a small demo app using .NET 6 with EF core and I am using Serilog to log exceptions into my database.
Everything is working fine but now I want to display the log table details on my ...
- 571
1
vote
1
answer
552
views
count values in json array per ID on multiple rows w/ OPENJSON
I have a table containing Id and JsonData columns (table has 100's of rows)
JsonData contains an array with various contact ID's for each Id
{"contacts":[{"id":"7d18e3c1-6849-...
- 27
0
votes
0
answers
166
views
Join large table with JSON data with another table
We are using Azure SQL database having tables for saving document information, document approvers, package information etc.
In document information table we save metadata in JSON string since metadata ...
0
votes
1
answer
409
views
SQL Server OpenJson - retrieve row based on nested Json, querying multiple Json rows
Given the data table below, how can I retrieve only the row #3, querying the field "chave", based on multiple json rows?
I want to retrieve the master row where the json field (NomeCampo = ...
- 6,903
1
vote
0
answers
2k
views
Multiple cross apply in SQL for a row with json
I have a row:
id, shipping_data, line_data
0, '[{"name": "Greg", "Address": "test address"}]', '[{"products": "apple", "cost": &...
- 57
0
votes
1
answer
170
views
OpenJson store processed object in original form
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice&...
- 65
0
votes
1
answer
203
views
Why does recreate table make query for the table faster in Azure Synapse Analytics?
Recently, I found a strange behaviour in Azure Synapse Analytics while attempting to tune a store procedure for better performance.
We have a slow performance issue when trying to parse 200 MB of JSON ...
- 271
0
votes
1
answer
248
views
SQL Server 2019 - Build JSON with spread operator
I'm required to supply a json object like this:
[
{
id: '59E59BC82852A1A5881C082D5FFCAC10',
user: {
...users[1],
last_message: "16-06-2022",
...
- 1,045