Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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": "[email protected]",
    "phone": 123456798,
    "address": {
        "address1": "1 Oak Grove",
        "address2": "London",
        "address3": "UK"
    },
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

I've tried 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 creates this (address section is not nested (but accredidation is):

[
  {
    "websiteURL": "www.test.edu",
    "email": "[email protected]",
    "phone": 123456798,
    "address.address1": "1 Oak Grove",
    "address.address2": "London",
    "address.address3": "UK",
    "accreditations": [
      {
        "name": "Indicator1",
        "value": "True"
      },
      {
        "name": "Indicator2",
        "value": "False"
      },
      {
        "name": "Indicator3",
        "value": "False"
      }
    ]
  }
]

FOR JSON PATH creates this (address section is nested, but accreditation is not - the whole block repeats):

[
  {
    "websiteURL": "www.test.edu",
    "email": "[email protected]",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator1",
    "value": "True"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "[email protected]",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator2",
    "value": "False"
  },
  {
    "websiteURL": "www.test.edu",
    "email": "[email protected]",
    "phone": 123456798,
    "address": {
      "address1": "1 Oak Grove",
      "address2": "London",
      "address3": "UK"
    },
    "name": "Indicator3",
    "value": "False"
    }
]

I think the key to it is some sort of FOR JSON sub query around the accreditations but I haven't had any success with this.

Create sample data with the following:

    /****** 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'test', N'www.test.edu', N'[email protected]', 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'something', N'https://something.edu/fulltime', N'[email protected]', 987654321, N'23 Tree Road', 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.uk/', N'[email protected]/', 123123123, N'57 Bonsai Lane', N'London', N'UK')
GO
like image 980
Jims Jams Avatar asked Sep 11 '18 21:09

Jims Jams


People also ask

What is the difference between JSON Auto and JSON path?

When a query references only one table, the results of the FOR JSON AUTO clause are similar to the results of FOR JSON PATH . In this case, FOR JSON AUTO doesn't create nested objects. The only difference is that FOR JSON AUTO outputs dot-separated aliases (for example, Info.

What is SQL Openjson?

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns.

Is it OK to store JSON in SQL database?

You can store JSON documents in SQL Server or SQL Database and query JSON data as in a NoSQL database.

What is Isjson in SQL Server?

Specifies the JSON type to check in the input. Valid values are VALUE, ARRAY, OBJECT or SCALAR. Introduced in SQL Server 2022 (16. x) Preview.


1 Answers

You need to use a subquery to produce a property that has a list for a value. Use an alias for the subquery that is the name of the property on the resulting JSON object.

This should do it for you:

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;

(As a side note, you should discontinue use of the old implicit JOIN syntax.)

like image 87
squillman Avatar answered Oct 21 '22 18:10

squillman