Choosing between FOR JSON PATH and FOR JSON AUTO in SQL Server can have a significant

I'm encountering an issue in SQL Server with creating nested JSON structures. My goal is to generate an output that resembles the following:

[
  {
    "websiteURL": "www.example.edu",
    "email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1d75787171725d69786e6933787968">[email protected]</a>",
    "phone": 123456798,
    "address": {
        "address1": "1 Maple Street",
        "address2": "New York",
        "address3": "USA"
    },
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

I've attempted using both FOR JSON AUTO and FOR JSON PATH:

SELECT
  d.SCHOOL_WEBSITE AS websiteURL
  ,d.SCHOOL_EMAIL AS email 
 ,d.SCHOOL_TELEPHONE AS phone
 ,d.[Address 1] AS 'address.address1'
 ,d.[Address 2] AS 'address.address2'
 ,d.[Address 3] AS 'address.address3'
 ,accreditations.[IndiUID] as name   
 ,accreditations.Value as value 
 FROM [TESTDB].[dbo].[DataValues] as d,[TESTDB].[dbo].[accreditations] as accreditations
 WHERE d.Code = accreditations.SchoolCode
 FOR JSON AUTO --PATH

FOR JSON AUTO yields this result (address section not properly nested but accreditation is):

[
  {
    "websiteURL": "www.example.edu",
    "email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="c5ada0a9a9aa85b1a0b6b1eba0a1b0">[email protected]</a>",
    "phone": 123456798,
    "address.address1": "1 Maple Street",
    "address.address2": "New York",
    "address.address3": "USA",
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

FOR JSON PATH results in this output (address section properly nested but accreditation is repeated for each block):

[
  {
    "websiteURL": "www.example.edu",
    "email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="d5bdb0b9b9ba95a1b0a6a1fbb0b1a0">[email protected]</a>",
    "phone": 123456798,
    "address": {
      "address1": "1 Maple Street",
      "address2": "New York",
      "address3": "USA"
    },
    "name": "Indicator1",
    "value": "True"
  },
  {
    "websiteURL": "www.example.edu",
    "email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="137b767f7f7c53677660673d767766">[email protected]</a>",
    "phone": 123456798,
    "address": {
      "address1": "1 Maple Street",
      "address2": "New York",
      "address3": "USA"
    },
    "name": "Indicator2",
    "value": "False"
  },
  {
    "websiteURL": "www.example.edu",
    "email": "<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="ef878a838380af9b8a9c9bc18a8b9a">[email protected]</a>",
    "phone": 123456798,
    "address": {
      "address1": "1 Maple Street",
      "address2": "New York",
      "address3": "USA"
    },
    "name": "Indicator3",
    "value": "False"
    }
]

I believe a FOR JSON subquery around the accreditations section might be the solution, however, I haven't been successful with this approach.

Please create sample data by using the following script:

    /****** Object:  Table [dbo].[accreditations]    Script Date: 11/09/2018 22:29:43 ******/
CREATE TABLE [dbo].[accreditations](
    [SchoolCode] [nvarchar](255) NULL,
    [IndiUID] [nvarchar](255) NULL,
    [Value] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[DataValues]    Script Date: 11/09/2018 22:29:44 ******/
CREATE TABLE [dbo].[DataValues](
    [Code] [nvarchar](255) NULL,
    [SCHOOL_NAME_FORMAL] [nvarchar](255) NULL,
    [SCHOOL_WEBSITE] [nvarchar](255) NULL,
    [SCHOOL_EMAIL] [nvarchar](255) NULL,
    [SCHOOL_TELEPHONE] [float] NULL,
    [Address 1] [nvarchar](255) NULL,
    [Address 2] [nvarchar](255) NULL,
    [Address 3] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'ABC', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator2', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'DEF', N'Indicator3', N'False')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator1', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator2', N'True')
GO
INSERT [dbo].[accreditations] ([SchoolCode], [IndiUID], [Value]) VALUES (N'GHI', N'Indicator3', N'True')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'ABC', N'institution', N'www.example.edu', N'<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a9c1ccc5c5c6e9ddcdaddffffccd">[email protected]</a>', 123456798, N'1 Oak Grove', N'London', N'UK')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'DEF', N'school', N'https://example.com/fulltime', N'<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f49c9198989bb4879b99919999899a91dc91919a">[email protected]</a>', 987654321, N'23 Elm Street', N'Paris', N'France')
GO
INSERT [dbo].[DataValues] ([Code], [SCHOOL_NAME_FORMAL], [SCHOOL_WEBSITE], [SCHOOL_EMAIL], [SCHOOL_TELEPHONE], [Address 1], [Address 2], [Address 3]) VALUES (N'GHI', N'university', N'http://www.university.ac/', N'<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="533b363f3f3c13263d317567333c26383f38277a3639303717743700363a37383029747b393632">[email protected]</a>/', 123123123, N'57 Ocean Avenue', N'London', N'UK')
GO
jsonsql-serverfor-json

Answer №1

To generate a property with a list as its value, you'll need to utilize a subquery and assign an alias that matches the desired property name in the resulting JSON object.

Here's how you can achieve this:

SELECT
    d.SCHOOL_WEBSITE AS 'websiteURL',
    d.SCHOOL_EMAIL AS 'email ',
    d.SCHOOL_TELEPHONE AS 'phone',
    d.[Address 1] AS 'address.address1',
    d.[Address 2] AS 'address.address2',
    d.[Address 3] AS 'address.address3',
    (
        SELECT 
            [IndiUID] as 'name',
            Value as 'value' 
        FROM [dbo].accreditations as ac
        WHERE ac.SchoolCode = d.Code
        FOR JSON PATH
    ) AS accreditations
FROM dbo.DataValues d
FOR JSON PATH;

(By the way, it's recommended to avoid using the old implicit JOIN syntax.)

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Sparks: Unlocking the Power of Transformative Merging

I am faced with a task involving 1000 JSON files that require individual transformations followed by merging into a single output file. The merged output must ensure no duplicate values are present after overlapping operations have been performed. My appr ...

javajsonapache-sparkrdd

Perform an HTTP/1.1 GET call on an Android device

I'm currently in the process of making a request to an API. The API has certain requirements that must be adhered to: The Base URL for our API can be found at (make sure to note the version number 1 after the /) All requests to the API should be ma ...

javaandroidjsonapi

Secure User Verification in Laravel 5 and AngularJs sans the need for JWT Tokens

I am currently working on a project that involves utilizing a Laravel-based back-end and a front-end built with both Laravel and AngularJS. After completing 40% of the back-end development, I am now faced with the task of implementing the front-end. Howev ...

phpangularjsjsonlaravel-5.1

What is the process for including a library in a Java project that does not have JAR files?

Recently, I developed a Java project with the intention of incorporating a JSON library. After conducting some research on various JSON libraries available, I noticed that none of them provided JAR files as part of their package. Undeterred, I proceeded t ...

javajson

What is the process for transforming a self-iterating ArrayList into JSON with the help of Jackson

I need to transform a List of data into the JSON structure shown below. I have retrieved the data from MySQL into an ArrayList and defined the EducationDTO POJO class. { "id": "1", "name": "EDUCATION", "data": "", "children": [ { "id": "1.1", ...

javajsonjackson

Parsing Json with Unicode characters on Android is quite a task

My SQL server collation is UTF8 Unicode. My PHP works fine and my Android project is working fine with normal characters, but in the case of Arabic characters, it displays an error "Error parsing JSON" on line 233 in the activity listed below. I have tried ...

androidjsonunicode

Transmitting an Array in JSON Format using PHP (Campaign Monitor)

Hey there, I have this interesting JSON Array called sample.txt. It's obtained from a sweepstakes form that collects user details like name and email. The WooBox sends the JSON Array with information for each entry. In this case, we have two entries w ...

phparraysjsonemailcampaign-monitor

From JSON Objects to PHP Mistakes

I created a PHP script to display an entire layout of divs with the necessary content. However, upon execution, the result is just a blank page. What could be the issue? Code: <? $url = 'http://socialclub.rockstargames.com/crewapi/the_greeks_360/ ...

phpjsonobject

To identify a specific field within a JSON file by cross-referencing it with a plain text file and extracting the corresponding values

Having two files, file1.json and a plain text file called file2. I need to compare the values in file2 with the values in file1.json and extract the corresponding CaseID from file1 based on matching values. Below are some cases along with the expected resu ...

jsonawksedfile-comparison

A guide to efficiently extracting the accurate ID from JSON using JSONPath syntax in Gatling

My current testing setup involves Gatling for API testing. In one of my scenarios, I need to extract an ID from a JSON response and save it in a variable. However, Gatling doesn't directly support this operation. The specific ID I am looking for corre ...

jsonscalagatling

Internet Explorer experiencing problems with JSON when using ajax请求

After struggling with this issue for a significant amount of time, I have decided to seek help from the community. I am utilizing Google Custom Search REST API in combination with jQuery to retrieve results and display them in the browser. The problem I a ...

jqueryajaxjsoninternet-explorergoogle-custom-search

One way to have a Spring RESTful API return JSON in its true format rather than as a string is by serializing the

Currently, I am developing a RESTful API using Spring. The API structure is such that it displays all objects of its corresponding type. You can access the API at the following link: The Data Transfer Object (DTO) for this API is as follows: public class ...

jsonspringrest

Utilize Java to extract information from a JSON file

I am currently attempting to navigate through a JSON file using Java, but unfortunately I've hit a snag due to the complex structure of the file. If you want to take a look at the file yourself, you can download it here: Reddit JSON file Specificall ...

javajson

Looping through a Map in JSP containing a nested List of lists of Strings

Looking to loop through a Map<String, List> in Java: final JSONObject mappedData = new JSONObject(); final Map<String, List<List<String>>> associatedUsecasesMap = new HashMap<String, List<List<String>>>(); // Code ...

jspjson

Saving data submitted through a form using React Hooks and JSON encounters problems with proper storage

I am facing an issue with a form that is supposed to create items labeled as "post" with unique IDs, titles, authors, and content. The desired structure is as follows: { "posts": [ { "id": 1, "title": "F ...

reactjsjsonformsreact-hooksreact-router

Leveraging the Yahoo Weather API

I have integrated the Yahoo Weather API and successfully retrieved data using the following link: Now, I am looking to implement this URL with Retrofit. Could you please guide me on how to dynamically change the city by passing a query? Thank you! ...

androidjsonretrofit2yahoo-weather-api

Navigating through a collection of generic dictionaries in C#

I'm dealing with a json object that has been converted into a list of Dictionaries. The structure of the json is as follows: { "DataList": {"Non Fuel": { "sn":"/DataXmlProduct/Customers/DataXml/Customer/DueDate", "ItemCode":"/DataXmlProduct/Cus ...

c#jsondictionary

What is the best way to include a RecyclerView row in the WishList feature?

I am looking to incorporate a Wishlist feature similar to Google Play in my app. Each row in my RecyclerView within the QuestionActivity contains a Button (Heart Icon). My goal is for when this heart icon is clicked, the corresponding row in the Recycler ...

androidjsonandroid-recyclerview

transform a JSON file containing multiple keys into a single pandas DataFrame

I am trying to convert a JSON file into a DataFrame using json_normalize("key_name"). I have successfully converted one key into a DataFrame, but now I need to convert all keys into one single DataFrame. { "dlg-00a7a82b": [ { "ut ...

pythonjsonpandasdataframedata-analysis

Is it feasible to deliver a push notification on iOS without utilizing the "alert" attribute in a JSON payload?

Is there a way to send a JSON format without including the "alert" attribute and still have the notification appear? I've attempted to remove the alert attribute, but it seems to prevent the notification from showing up. Any suggestions on how to hand ...

iosjsonparse-platform