Questions tagged [sql-server-json]
58 questions
0
votes
1 answer
SQL Server where Clause on JSON Field
I have a table
ID | Start Date | End Date | Summary
---+-------------------+-------------------+----------------------------
1 | 2020-01-01T09:20 | 2020-01-01T09:30 | {"total":20,"totalError":10}
1 | 2020-01-02T09:20 |…
Ali
- 91
- 1
- 8
0
votes
1 answer
Dynamic JSON Parsing using T-SQL
I have a highly-unstructured JSON string, coming as a response of calling a REST API:
{
"info": "Test Json Structure",
"Owner": "Self-Owned",
"managedObjects": [{
"Name": "Device1",
"Class": "A"
}, {
…
khidir
- 43
- 1
- 8
0
votes
1 answer
SQL Server FOR JSON PATH
So, I have a table with data like this
PersonID | Name | Address | Skills
---------+------+---------------+------------
1 | XYZ | Test Address | NA
2 | ABC | Test Address2 | Programming
I want to transform data into JSON with…
Ali
- 91
- 1
- 8
0
votes
1 answer
T-SQL to retrieve part of data such as array using FOR JSON PATH
I am trying to retrieve the data from our SQL server in a specific way, basically I have a column and it has a delimiter ',' and need to convert like an array using FOR JSON PATH
I have tried using FOR XML PATH and FOR JSON PATH, but I don't figure…
José Luiz
- 334
- 1
- 5
- 18
0
votes
1 answer
How to query nested fields and return with the path?
Sorry for my bad english and low vocabulary. How to filter-out all fields with "Properties.Searchable" = "true" ? The fields can be a child field of another.
Here is an example(removed most of unnecessary data):
{
"Configuration": {
"Fields": {
…
eSPiYa
- 882
- 1
- 12
- 29
0
votes
3 answers
How to I check each item of a json array within the where clause using a like statement
I am creating a query which is for a search feild within my application. The query uses the "like" key word to check a variety of fields within the records. One of the fields is an unnamed ([{}, {}]) json array. The fields within the array all…
Troy Crowe
- 123
- 2
- 12
0
votes
2 answers
How to join JSON to table (SQL Server)
I am trying to join an array of JSON to a table in SQL Server (in hopes of avoiding using TVP).
Table Data
| item_id | qty |
|-----------|-----|
| dur 20001 | 1 |
| dur 93677 | 1 |
SQL
declare @json nvarchar(max) = '[{
"order":{
…
Matthew
- 1,461
- 3
- 23
- 49
0
votes
2 answers
I have a column in a data frame full of JSON format with varying length in each row
I have dataframe column containing JSON format of varying length(i mean the values), i have used--fromJSON function and parse it, but it contains multiple nested lists and I don't know how to separate each value in the JSON into a multiple…
rock4sridhar
- 31
- 3
0
votes
2 answers
Sql Server - Format Query Result for an Integer datatyped Column of a table as Array
When I run the following query, the result looks like the one provided in screenshot below.
DECLARE @numArray VARCHAR(50) = '[1,2,45,1,21,45,32,564,48,56,98,87]';
SELECT value AS Number FROM OPENJSON(@numArray)
I was curious if it is possible…
Prabesh
- 353
- 1
- 6
- 20
0
votes
1 answer
Can we optionally use `JSON Path` in SQL Server 2016
I want to return JSON or a Table result set (normal) based on a parameter that I am passing to a SQL Server stored procedure.
SELECT
emp.Name,
emp.EmpID
FROM
Employee emp
ORDER BY
emp.Name
FOR JSON Path;
This will…
Sreekumar P
- 5,900
- 11
- 57
- 82
-1
votes
1 answer
SQL Server Dynamic insert script JSON For
43/5000
First of all I apologize for my bad english...
I am trying to write the insert procedures of all tables with a single procedure. But I have a problem like this.
DECLARE @jsondata varchar(MAX)
Set…
JeHM
- 1
- 2
-1
votes
1 answer
Trying to figure out how to make child array with JSON Path Expressions
Need guidance on how to make EmailId a child array of EventId with JSON Path Expressions using the following schema:
http://sqlfiddle.com/#!6/8f8ecc/2
joey0xx
- 73
- 10
-2
votes
1 answer
How can I read below Json column stored in SQL Server using openjson?
Declare @ResponseText nvarchar(4000)
set @responseText ='{
"submissions": [
{
"xml_id":"id_x5d94851726b470.68571510",
"fields": [
{"fieldvalue":"customerEmail@xyzdomain.com","fieldid":"57282490"},
…
Dia Rashid
- 13
- 1