Using sub queries to create JSON file

Question

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 following sub query but SQL doesn't like the formatting.

` SELECT

'Admin User TEST ONLY PLEASE IGNORE' AS AdditionalNotes


(
   SELECT v.atFault 
   FROM dbo.ic_DP_AX ax
   CROSS APPLY (VALUES (ax.Acc_fault1), (ax.Acc_fault2)) v (atFault)
   FOR JSON AUTO
   ) AS InsuredPartyClaims,
   (
   SELECT Acc_fault3 AS atFault 
   FROM dbo.ic_DP_AX
   FOR JSON AUTO
   ) AS InsuredPartyConvictions
FOR JSON PATH) ROOT('InsuredParties')



FROM

    dbo.icp_Daprospect AS p INNER JOIN
    dbo.icp_Dapolicy AS d ON p.Branch@ = d.Branch@ AND p.ClientRef@ = 
 d.ClientRef@ LEFT OUTER JOIN
    dbo.ic_DP_AX AS  ax ON P.Branch@ = ax.B@ AND ax.PolRef@ = d.PolicyRef@ 
LEFT OUTER JOIN
WHERE 
    d.PolicyRef@ = '' AND
    d.Branch@ = 0`

FOR JSON PATH

The output I'm trying to achieve is:

"InsuredParties": [
    {
        "InsuredPartyClaims": [
            {
                "atFault": false
            },
            {
                "atFault": true
            }
        ],
        "InsuredPartyConvictions": [ 
           {
                "atFault": false
           },

Can anyone see what I'm doing wrong? I'm trying to keep this as simple as possible.

Hi Andrew! Just at first glance, I think you need a SELECT statement after your CROSS APPLY. SELECT x FROM y CROSS APPLY ( SELECT x FROM z WHERE ID = ID) as A. You're essentially doing an inner join so you need a SELECT statement in your CROSS APPLY. Not sure if this will help but some refs on CROSS APPLY mssqltips.com/sqlservertip/1958/… — Katherine
– Katherine, Commented Sep 17, 2020 at 14:03

Zhorov · Accepted Answer · 2020-09-17 12:02:37Z

1

It's always difficult without sample data, but the foolowing example is a possible solution:

Table:

CREATE TABLE dbo.ic_DP_AX (Acc_fault1 bit, Acc_fault2 bit, Acc_fault3 bit)
INSERT INTO dbo.ic_DP_AX (Acc_fault1, Acc_fault2, Acc_fault3)
VALUES (0, 1, 0)

Statment:

SELECT
   (
   SELECT v.atFault 
   FROM dbo.ic_DP_AX ax
   CROSS APPLY (VALUES (ax.Acc_fault1), (ax.Acc_fault2)) v (atFault)
   FOR JSON AUTO
   ) AS InsuredPartyClaims,
   (
   SELECT Acc_fault3 AS atFault 
   FROM dbo.ic_DP_AX
   FOR JSON AUTO
   ) AS InsuredPartyConvictions
FOR JSON PATH, ROOT('InsuredParties')

Result:

{
  "InsuredParties":[
    {
      "InsuredPartyClaims":[
        {
          "atFault":false
        },
        {
          "atFault":true
        }
      ],
      "InsuredPartyConvictions":[
        {
          "atFault":false
        }
      ]
    }
  ]
}

answered Sep 17, 2020 at 12:02

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Barnard Over a year ago

unfortunately I also get a syntax error at the two FOR JSON AUTO statements

Zhorov Over a year ago

@AndrewBarnard The statement from the answer is a working example. If possible, post your attempt.

Andrew Barnard Over a year ago

I've just amended my original post to include the full query

SteveC · Accepted Answer · 2020-09-17 11:48:30Z

0

The subqueries need to return JSON as well.

Try

(
  (SELECT ax.Acc_fault1 AS [atFault] FROM dbo.ic_DP_AX AS ax FOR JSON PATH) AS [PartyClaims]
  (SELECT ax.Acc_fault2 AS [atFault] FROM dbo.ic_DP_AX AS ax FOR JSON PATH) AS [PartyClaims]
  (SELECT ax.Acc_fault3 AS [atFault] FROM dbo.ic_DP_AX AS ax FOR JSON PATH) AS [PartyConvictions]
) FOR JSON PATH AS [InsuredParties]

answered Sep 17, 2020 at 11:48

SteveC

6,0252 gold badges14 silver badges26 bronze badges

1 Comment

Andrew Barnard Over a year ago

Unfortunately when I try to use this it displays a syntax error.

Collectives™ on Stack Overflow

Using sub queries to create JSON file

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related