Questions tagged [sql-server-json]
58 questions
3
votes
0 answers
SQL Server Index on JSON containing dynamic fields or array
We have an AuditEntity table where all changes are stored.
The table has an OldValues and NewValues column, which contains json of the altered fields.
I want to query this table so it gives me all rows where a particular field was modified, for…
Guillaume Morin
- 3,910
- 6
- 25
- 40
2
votes
1 answer
Create json key value from table column name and data
Is it possible to create JSON key value from a table SELECT statement, where column name as key and the column value as value
declare @T table(Id int, ItemName varchar(10), CategoryId int, ItemDate date)
insert into @T
values(1,'ABC',100,…
user12073359
- 289
- 1
- 11
2
votes
2 answers
SQL Server parse JSON to update another table
I have a table with JSON data in one of the columns and i'm trying to parse the JSON data and insert into a temp table
DECLARE @TEMPTABLE
(
ID INT,
Status NVARCHAR(50),
Cost DECIMAL(20, 0)
)
INSERT INTO @TEMPTABLE
SELECT
…
RData
- 959
- 1
- 13
- 33
2
votes
1 answer
Get SQL server record details as JSON for each record individually
I have 2 tables. Department & Employee as below :
Department
|----------------------|
| Id | Name |
|---------|------------|
| 10 | Admin |
|---------|------------|
| 11 | IT |
…
Rasmita Dash
- 917
- 5
- 15
- 28
2
votes
1 answer
JSON from SQL query with child level
I have a simple query in my database:
SELECT id, name FROM users FOR JSON AUTO, ROOT('users')
This returns the following JSON:
{
"users": [
{"id": "1", "name": "John"}
{"id": "2", "name": "Mike"}
]
}
I want to have the…
Danilo Körber
- 858
- 1
- 7
- 27
2
votes
1 answer
How to I return multiple rows from a nested JSON object stored in a table
Given the following JSON stored in a nvarchar(max) column, how to generate the table shown? I can make is for if the nested object is an [], an array, but not a structure {}
DECLARE @JSON AS NVARCHAR(MAX);
SET @JSON = N'{
"ACCOUNT": 1
, …
Peter
- 95
- 7
2
votes
1 answer
Testing to see if a value exists in a nested JSON array
I have a SQL 2016 table that contains a column holding JSON data. A sample JSON document looks as follows:
{
"_id": "5a450f0383cac0d725cd6735",
"firstname": "Nanette",
"lastname": "Mccormick",
"registered": "2016-07-10T01:50:10…
Randy Minder
- 47,200
- 49
- 204
- 358
1
vote
1 answer
Dynamic insert/update json array request to database
I have question about how to update/insert Json value into SQL Server. My logic was: frontend sends dynamic Json array value to me i.e they send Json array value (it have both insert and update value).
This is a sample Json:
[
{
"CID": 8,
…
Dotnet
- 63
- 1
- 10
1
vote
1 answer
How to query a key in a SQL Server Json column if it could be a scalar value or an object?
I have a table that with a column Info VARCHAR(MAX) constrained to always be valid json. What is the best way to query a key with JSON_QUERY/JSON_VALUE if I don't know ahead of time if the value associated with the key is a scalar or not? Currently…
will smith
- 13
- 3
1
vote
2 answers
SQL Server FOR JSON PATH, Exclude specific field if null
I have table with data like this
Id | Name | Phone | OtherField
----+---------+--------+-----------
1 | ABC | 12344 | NULL
2 | XYZ | NULL | NULL
I want a SQL query to transform it like this
[
{
"ID":1,
…
Ali
- 91
- 1
- 8
1
vote
3 answers
MS SQL Query a field containing JSON
I have the following JSON in a SQL field in a table:
{
"type": "info",
"date": "2019/11/12 14:28:51",
"state": {
"6ee8587f-3b8c-4e5c-89a9-9f04752607f0": {
"state": "open",
"color": "#0000ff"
}
…
Pieter Coetzer
- 75
- 2
- 8
1
vote
1 answer
SQL view / query to join data between 2 tables via a json field
Example table structure:
create table issues
(id int, title varchar(50), affectedclients varchar(max))
create table clients
(id int, name varchar(50))
insert into issues (id, title, affectedclients) values (1, 'Error when clicking save',…
jasear
- 13
- 3
1
vote
1 answer
How to select string Rows from MS SQL Json String array?
I have a Text String which contains JSON, something like this:
'{ "d" : [ "test0", "test1", "test2" ] }'
and I would like to retrieve the item of the Array as rows.
+------------+
| data |
+------------+
| test0 |
| test1 |
| …
winner_joiner
- 12,173
- 4
- 36
- 61
1
vote
1 answer
Update JSON using JSON_MODIFY
I store JSON data in SQL Server.
My table looks like:
Table name: JsonData
Columns: ID, Data
My JSON looks like:
{
"data": [{
"identifier": 1,
"someData": {
"sample1": "lorem 1",
…
mskuratowski
- 4,014
- 15
- 58
- 109
1
vote
2 answers
Cannot use JSON_QUERY in WHERE clause for SQL Server
I have a table: Employee.
Schema: (name varchar, preferences nvarchar(max)). where preferences is a json formatted string.
My query (that fails) is:
select JSON_QUERY(preferences, '$.personal')
from Employee
where ISJSON(preferences) = 1
and…
mljohns89
- 887
- 1
- 11
- 16