Questions tagged [sql-server-json]
58 questions
1
vote
1 answer
SQL Server : update table with join condition using JSON input
I have a table with columns position_id and column_id. I am getting a JSON response from an API call, and I want to update my table using the value from the JSON response using the position_id.
Here is an example
JSON Table:
Table in database(I…
ProgSky
- 2,530
- 8
- 39
- 65
1
vote
1 answer
How to replace complete object within JSON Object in Sql server
I am trying to modify existing json in sql and adding a new property in each object in array.
My json is as given below
{"Key":"Employee", "Data":[{"id": "1", "value": "Vogel"},{ "id": "2", "value": "Vogel"}]
Now I am trying to add a new property…
manish
- 61
- 8
1
vote
1 answer
Conditionally using FOR JSON PATH in stored procedure
I would like to create stored procedures that conditionally output JSON based on an input parameter. Can this be done without having to duplicate the select statement?
CREATE PROCEDURE myProcedure @outputJson bit
AS
BEGIN
IF outputJson =…
ddrjca
- 472
- 2
- 15
1
vote
1 answer
SQL Server : update json from another json – generically
In SQL Server, how to update/merge a json with another json, without explicitly using the keys / defining columns?
Some background: I store metadata as a json in a varchar(max) column. Each record can have different metadata keys in the same table.…
Shawn Elihis
- 173
- 1
- 7
1
vote
2 answers
Table to JSON with specified format
I have the following query and trying for the JSON format specified below:
SELECT
Convert(VARCHAR(10),[Date],110) HolidayDate
FROM
dbo.Holidays FOR JSON AUTO, ROOT('Holidays')
Current Output
{
"Holidays": [
{
"HolidayDate":…
Raj
- 31
- 3
1
vote
1 answer
JSON Query return result with escaped character
From the following query I am getting result as :
"Units": "[{\"Name\":\"Unit25\"}]",
Query is as follow query which reproducing this result, I have simplified the query actual query is used but based selection is this:
SELECT (
SELECT *
…
Furqan Misarwala
- 1,743
- 6
- 26
- 53
1
vote
1 answer
Retrieving a specific JSON array from a JSON array of values
I have a SQL table that contains a column holding JSON data. One of the JSON values looks as follows:
{
"_id": "5a450f038104ca3cb0ff74b5",
"index": 3,
"guid": "20d807c5-bddc-44b9-97fe-fd18af1b6066",
"isActive": false,
"balance":…
Randy Minder
- 47,200
- 49
- 204
- 358
1
vote
2 answers
How to create JSON Array Inside JSON object using FOR JSON SQL Server 2016
How to create JSON Array Inside JSON object using FOR JSON SQL Server 2016 (TABLE to JSON)
Here is my query:
SELECT
m.MeetingId AS tblMeeting_MeetingId,
m.Attended AS tblMeeting_Attended,
m3.CompanyId AS tblMeetingAttendants_CompanyId,…
Nayanish Damania
- 542
- 5
- 13
1
vote
3 answers
Get Json Output with two select query in SQL Server
I would like to output the results of a SELECT statement as a JSON object.
id name active Status
1 Bob Jones 1 Active
2 John Smith 0 Inactive
I need return result like this:
{"Active" :[{"id":1,"name":"Bob Jones"…
Robin Bronston
- 147
- 14
1
vote
1 answer
Append Values to JSON object in SQL 2016 / Modify JSON object (SQL Server JSON Data)
I have a JSON column in SQL2016 database, and I want to add a key-value pair to the root of the JSON object.
The content of the JSON column should be altered from
{"Info1":"Value1"}
to
{"Info1":"Value1", "Info2":"Value2"}
d.popov
- 4,175
- 1
- 36
- 47
0
votes
1 answer
Query json array with no property names
I have a nvarchar(max) column that contains a string array of tags. I am querying this columns for matching values using the JSON functions. While I do have it working, I wasn't sure if there was a better way using other built-in functions.
IF(NOT…
Tony Basallo
- 3,000
- 2
- 29
- 47
0
votes
2 answers
How can I convert this Json array in SQL Server?
I have an array like this and I want to extract its count into a single array. Can I do this in SQL Server?
[
{
"ItemName": "ITEM1",
"Details": [
{
"column1": "2021\/Apr",
"Count": 10
},
{
…
shan127
- 19
- 5
0
votes
1 answer
How to query a table with JSON column with key-value pairs to match all keys and values
Say I have an Image table with a meta column in JSON:
Id
Meta
1
{ "size": 80, "effect": "blur" }
2
{ "size": 200, "optimize": true }
3
{ "color": "#abcdef", "ext": ".jpg" }
And I have a dynamic param of table type like…
icube
- 2,578
- 1
- 30
- 63
0
votes
1 answer
How to read a complex json array with SQL Server
This is my query:
SELECT
JSON_QUERY(MyStringColumnWithJson, '$.Images') AS images
FROM MyTable
which returns a single field with the JSON data shown here below:
"{
"Images":
[
…
HelloWorld
- 4,671
- 12
- 46
- 78
0
votes
2 answers
Insert rows into SQL Server table from json
Trying to work up a stored procedure to insert json as rows into an existing empty table. The json properties match the table columns.
This stored procedure returns 3 errors shown below the following code
declare @json nvarchar(max)
declare @db_name…
Vague
- 2,198
- 3
- 18
- 46