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

Improve this question
6
  • What have you tried so far? Commented Jun 21, 2018 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 Commented Jun 21, 2018 at 16:23
  • What is the problem with the query that you tried? Commented Jun 21, 2018 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"} Commented Jun 21, 2018 at 16:31
  • Can you edit your question to add these details? Commented Jun 21, 2018 at 16:38
Related questions
1
Sql query to json result
1
How to extract value JSON data from the Table?
0
SQL JSON Table Issue
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.