Stack Overflow
Stack Overflow

Questions tagged [for-json]

The `FOR JSON` clause allows for exporting query results or table data to the JSON format from a T-SQL query in SQL Server. Available since SQL Server version 2016.

57 questions
12
votes
1 answer

FOR JSON PATH vs FOR JSON AUTO SQL Server

I'm having an issue creating nested JSON in SQL Server. I'm trying to create an output that looks like this: [ { "websiteURL": "www.test.edu", "email": "hello@test.edu", "phone": 123456798, "address": { "address1": "1 Oak…
Jims Jams
  • 125
  • 1
  • 1
  • 6
10
votes
1 answer

For JSON Path Formatting

Background: I have a JSON nvarchar(max) column named 'questions' that looks like this real example from a single…
Tim Halbert
  • 477
  • 1
  • 4
  • 9
7
votes
1 answer

SQL Server - "for json path" statement does not return more than 2984 lines of JSON string

I'm trying to generate huge amount of data in a complex and nested JSON string using "for json path" statement, and I'm using multiple functions to create different parts of this JSON string, as follow: declare @queue nvarchar(max) select @queue =…
Brian Salehi
  • 414
  • 2
  • 10
  • 19
6
votes
3 answers

SQL Server 2016 SSMS Json Formatting

I am working with JSON in SSMS in SQL Server 2016. Is there any way to get around that the results of a FOR JSON statement are presented as an XML column? Are there any settings changes, additional components, external tools, etc. or even hacks to…
WillC
  • 1,761
  • 17
  • 37
5
votes
3 answers

Get array of values instead of array of objects when using FOR JSON

I'm trying to flatten out an object array that is constructed by FOR JSON. My query looks like: select ( select id from MyTable where id in (select value from OPENJSON(@jsonArray)) FOR JSON PATH ) existing, …
Filip
  • 327
  • 1
  • 8
  • 18
4
votes
1 answer

FOR JSON PATH in CASE statement

When I use a variable (or a field from a table) in a case statement with "FOR JSON PATH", the JSON provided is not well formed. Ex: declare @MyValue nvarchar(50) set @MyValue='1' select CASE WHEN @MyValue='1' THEN (select 'ROLE_CLIENT_READONLY' as…
Tatou
  • 51
  • 2
4
votes
1 answer

SQL Generate JSON string using FOR JSON PATH with 2 ROOTs

I have a very simple table containing 5 columns and the table will only hold 1 record at a time. I'm to generate a JSON string from the record and send it to an endpoint. This is how the JSON string are to be formatted. As you can see it contains 2…
OJ Slott
  • 75
  • 6
3
votes
1 answer

How to aggregate and Join or Union into flat Json object with arrays?

I have data that looks like: Customers Table CustomerId CustomerName CustomerEmail ------------------------------------------ 1 Ben Ben@gmail.com 2 Robert Robert@gmail.com 3 Paul …
ttugates
  • 5,818
  • 3
  • 44
  • 54
2
votes
2 answers

Convert flat SQL rows into nested JSON array using FOR JSON

So, I have a simple view that looks like this: Name | Type | Product | QuantitySold ------------------------------------------------------ Walmart | Big Store | Gummy Bears | 10 Walmart | Big Store | Toothbrush | 6 Target | Small…
J. Minjire
  • 1,003
  • 11
  • 22
2
votes
2 answers

Using sub queries to create JSON file

I'm trying to create a JSON file for a new project that I'm currently looking into I've got most of it working as expected but I'm now at a point where I'm trying to use sub queries in order to format the JSON correctly. I've tried to use the…
Andrew Barnard
  • 21
  • 3
2
votes
1 answer

Omitting Scientific Notation in Numeric columns when working with Dynamic SQL returning JSON

The below dynamic query returns the output of the numeric columns in Scientific notation. DECLARE @Cols VARCHAR(MAX) = (SELECT STUFF(( SELECT ',' +'['+ Description + ']' FROM @PermittedColumnIDs DECLARE @Query NVARCHAR(MAX) = 'SELECT TOP 1000 '+…
Harsha W
  • 3,162
  • 5
  • 43
  • 77
2
votes
3 answers

Output json in dictionary (string-indexed list) notation from SQL Server

I have this result set in SQL server: ID CUSTOMER PRODUCT DATE COUNT A1 Walmart Widget 1/1/2020 5 B2 Amazon Thingy 1/2/2020 10 C3 Target Gadget 2/1/2020 7 I want to output it as json, which SQL server…
Devin Burke
  • 13,642
  • 12
  • 55
  • 82
2
votes
2 answers

Combine JSON objects from multiple rows into a single object

CREATE TABLE t(Id int, typee nvarchar(50), jsonStr nvarchar(max)); INSERT INTO t(Id, typee, jsonStr) VALUES (3786, 'APV', '{"1":1,"3":3,"4":24,"5":95}'), (3786, 'VN', '{"1":3,"5":25}'); -- Expected result -- {"APV": {"1":1,"3":3,"4":24,"5":95},…
Arooran
  • 637
  • 1
  • 17
  • 31
2
votes
2 answers

Is there a way to return either a string or embedded JSON using FOR JSON?

I have a nvarchar column that I would like to return embedded in my JSON results if the contents is valid JSON, or as a string otherwise. Here is what I've tried: select ( case when IsJson(Arguments) = 1 then Json_Query(Arguments) …
N8allan
  • 2,138
  • 19
  • 32
1
vote
2 answers

Fetching JSON from SQL when a column is empty

I have a table in SQL Server to keep all of the users app reviews. When the user reviews the app with 4 or 5 stars I recieve JSON like this one { "Calificacion": 5, "Cuestionario": "", "Comentarios": "", "Application":…
Rodrigo Jimenez
  • 37
  • 8
1
2 3 4