Questions tagged [sql-server-json]
58 questions
10
votes
8 answers
Concatenate or merge two json objects in SQL Server
I have a table storing json in one column. I would like to update the json value by merging in another json.
Something like:
insert into mytable
values ('{ "a": "b" ')
update mytable
set jsonColumn = JSON_MERGE(jsonColumn, '{ "c": 2 }')
This…
Jan Blaha
- 3,069
- 1
- 22
- 35
9
votes
1 answer
Query complex JSON in SQL Server - filter array of objects
I'm having problems meshing together the JSON functions in Msft Sql Server. I have a table that stores complex JSON structures and need to pull out a subset of an array of objects.
As an example, I made a simple script that creates a table and…
Matthew Allen
- 538
- 2
- 7
- 14
8
votes
2 answers
Get names of keys from JSON in SQL Server
I've got the following JSON.
[
{
"attributes": {
"2003": "Some text",
"2004": 0,
"2006": 0,
"2008": 0,
"2011": 0,
"2120": 0
},
"path": "/Path1",
"changeDate":…
mburm
- 1,417
- 2
- 17
- 37
8
votes
4 answers
Where clause on json data in Sql Server 2016
I have a nvarchar(1000) field in my table and I am storing JSON data in that column.
eg :
CONTENT_RULE_ID CONTENT_RULE
1 {"EntityType":"Inquiry", "Values":[1,2]}
2 {"EntityType":"Inquiry", "Values":[1,3]}
3 …
Harisyam M
- 407
- 1
- 3
- 14
8
votes
3 answers
SQL Server JSON_Modify, How to Update all?
i am trying update all columns with a value with Json_Modify:
DECLARE @JSON NVARCHAR(MAX)
SET @JSON =
N'{
"A":1,
"TMP": [
{"A":"VALUE1", "B": "VALUE2", "C": 1},
{"A":"VALUE3", "B": "VALUE4", "C": 2},
{"A":"VALUE5", "B": "VALUE6",…
mdelphi
- 83
- 1
- 1
- 5
7
votes
3 answers
JSON text is not properly formatted. Unexpected character 'N' is found at position 0
I am new to JSON in SQL. I am getting the error "JSON text is not properly formatted. Unexpected character 'N' is found at position 0." while executing the below -
DECLARE @json1 NVARCHAR(4000)
set @json1 =…
user9057272
- 367
- 2
- 5
- 17
6
votes
3 answers
SQL Server FOR JSON Path Nested Array
We are trying to use FOR JSON Path in SQL Server 2016 for forming a Nested Array from a SQL Query.
SQL Query:
SELECT A,
B.name as [child.name],
B.date as [child.date]
from Table 1 join Table 2 on Table 1.ID=Table 2.ID FOR JSON PATH
Desired…
code_blue
- 523
- 2
- 9
- 19
4
votes
1 answer
TSQL: Recursive Descent in JSON Path
I was hoping to take advantage of $.. in json_value() function within TSQL queries using stored json documents. Unfortunately it doesn't work:
JSON path is not properly formatted. Unexpected character '.' is found at position 2.
and according to…
sharpener
- 1,383
- 11
- 22
4
votes
1 answer
SQL - Json query
I have tables similar to below where it has one to many mapping
DECLARE @T1 TABLE(RootId int, [Name] varchar(50))
DECLARE @T2 TABLE(Id int,RootId int, Category varchar(50))
INSERT INTO @T1
VALUES(1,'Some Name 12121'),(2,'Some Name 343434')
INSERT…
user12073359
- 289
- 1
- 11
4
votes
3 answers
Select row where nested JSON array contains certain value
I have a few database rows like this in the Employee table:
EmpId Name Information
1 Eric {“Experience”: [{“Title”: “Assistant Accountant”, “Company”: “ComA”, “YearsOfExperience”: 3}, {“Title”: “Accountant”, “Company”:…
april
- 89
- 1
- 12
4
votes
1 answer
How can I map the key in an openjson query with an explicit schema?
I have a query that takes an array of a explicit number of (let's say 100) arrays of a sequence of values.
the json looks something like this
[["horse",6],
...,
["dog",100]]
Each of these array elements maps directly to some other table. While I…
Michael B
- 7,512
- 3
- 31
- 57
4
votes
1 answer
How to make the field values of a SQL table to be the keys of JSON object in SQL?
I have been trying to get a JSON object from my DB in the format that I wanted it so I ran the following sql query:
SELECT PROJECTS.key_code AS CODE, PROJECTS.name AS Name,
PROJECTS.date AS Date, PROJECTS.descr AS Description
FROM…
Salem
- 119
- 1
- 11
4
votes
1 answer
OpenJson using a wildcard
I have a SQL query using OPENJSON to import JSON data into a table. My problem is that the data I need is nested. How can I use a wildcard in the JSON path to get what I need?
SELECT @Set =
BulkColumn FROM OPENROWSET
(BULK 'Sets.json', DATA_SOURCE…
Taylor LeMaster
- 133
- 2
- 9
3
votes
2 answers
SQL Server For JSON Path dynamic column name
We are exploring the JSON feature in SQL Sever and for one of the scenarios we want to come up with a SQL which can return a JSON like below
[
{
"field": {
"uuid": "uuid-field-1"
},
"value": {
"uuid": "uuid-value" //value…
Mritunjay
- 25,338
- 7
- 55
- 68
3
votes
1 answer
Can't parsing an array of objects with Dynamic Keys using OPENJSON And SQL Server 2017
I'm after running into some trouble parsing a set of JSON documents using SQL Server 2017.
I've encountered a nested array (sample below) within a document which uses dynamic ObjectId's i.e. 123 as the object Key as opposed to using a static key…
TheBeardedDBA
- 47
- 4