0

How to write the SQL query below mention table structure and JSON output using FOR JSON.

table structure 

CREATE TABLE [dbo].[TreeView](
[AuditId] [int] NOT NULL,
[Id] [int] NOT NULL,
[FatherId] [int] NULL,
[MotherId] [int] NULL,
[EnglishName] [varchar](500) NULL,
[Description] [nchar](10) NULL,
[Image] [varchar](500) NULL,

JSON output

 { id: 1, title: "Meera Mohaideen", description: "", image: "demo/images/photos/m.png" },
            { id: 2, title: "Rahman Beevi", description: "", image: "demo/images/photos/f.png" },
            { id: 3, title: "Abdul Jabbar", description: "", image: "demo/images/photos/m.png" },
            { id: 4, title: "Jahabar Natchiyal", description: "", image: "demo/images/photos/f.png" },
            { id: 5, parents: [1,2] ,title: "AJhir Ali", description: "", image: "demo/images/photos/m.png" },
          { id: 6, parents: [3, 4], title: "Kamila Begam", description: "", image: "demo/images/photos/f.png" },
           { id: 7, parents: [5, 6], title: "Simra", description: "7", image: "demo/images/photos/f.png" }

Query

SELECT Id AS Id, 
CASE WHEN ISNULL(FatherId,0) <> 0 OR ISNULL(MotherId,0) <> 0 THEN 
CAST(FatherId AS VARCHAR(25)) + ',' + CAST(MotherId AS VARCHAR(25)) END AS parents, 
EnglishName AS title, 
[Description] AS Description, 
[Image] AS Image FROM TreeView 
FOR JSON AUTO

I tried but result is {"Id":5,"parents":"1,2","title":"AJhir Ali"} but I am expecting this {"Id":5,"parents":[1,2],"title":"AJhir Ali"}

Thanks

AJhir Ali
  • 9
  • 3
  • What have you tried so far? – vendettamit Jun 21 '18 at 16:22
  • SELECT Id AS Id, CASE WHEN ISNULL(FatherId,0) <> 0 OR ISNULL(MotherId,0) <> 0 THEN CAST(FatherId AS VARCHAR(25)) + ',' + CAST(MotherId AS VARCHAR(25)) END AS parents, EnglishName AS title, [Description] AS Description, [Image] AS Image FROM TreeView FOR JSON AUTO – AJhir Ali Jun 21 '18 at 16:23
  • What is the problem with the query that you tried? – vendettamit Jun 21 '18 at 16:28
  • result is {"Id":5,"parents":"1,2","title":"AJhir Ali"} but I am expecting this {"Id":5,"parents":[1,2],"title":"AJhir Ali"} – AJhir Ali Jun 21 '18 at 16:31
  • Can you edit your question to add these details? – vendettamit Jun 21 '18 at 16:38
  • I found the solution from this [link](https://stackoverflow.com/questions/37494211/sql-server-2016-for-json-output-integer-array) Please answer my issue If some one know better solution. – AJhir Ali Jun 21 '18 at 18:56

0 Answers0