T-SQL - JSON Nested Objects - 1 row vs. multiple rows

Question

Here is the table definition:

key_en, text_en, html_text_en, key_fr, text_fr, html_text_fr

Here is my query:

SELECT  
    [key_en] AS 'en.key'
    ,[text_en] AS 'en.text'
    ,[html_text_en] AS 'en.htmltext'
    ,[key_fr] AS 'fr.key'
    ,[text_fr] AS 'fr.text'
    ,[html_text_fr] AS 'fr.htmltext'
FROM [MyContent]
FOR JSON PATH;

The result I get as JSON is correct.

[
    {
        "en": {
            "key": "key en",
            "text": "text en",
            "htmltext": "html en"
        },
        "fr": {
            "key": "key fr",
            "text": "text fr",
            "htmltext": "html fr"
        }
    },
    {
        "en": {
            "key": "key2 en",
            "text": "text2 en",
            "htmltext": "html2 en"
        },
        "fr": {
            "key": "key2 fr",
            "text": "text2 fr",
            "htmltext": "html2 fr"
        }
    }
]

However the result set is in a single row (very long string).

I'd like to spread the result set across as many rows as the data in the table. The expected result should be:

    ROW 1 
    {
        "en": {
            "key": "key en",
            "text": "text en",
            "htmltext": "html en"
        },
        "fr": {
            "key": "key fr",
            "text": "text fr",
            "htmltext": "html fr"
        }
    }

    ROW 2 
    {
        "en": {
            "key": "key2 en",
            "text": "text2 en",
            "htmltext": "html2 en"
        },
        "fr": {
            "key": "key2 fr",
            "text": "text2 fr",
            "htmltext": "html2 fr"
        }
    }

What changes do I have to bring to reach my goal?

Probably FOR JSON PATH, WITHOUT_ARRAY_WRAPPER will help. I think that the answers are correct. — Zhorov
– Zhorov, Commented Dec 17, 2019 at 12:44

Martin · Accepted Answer · 2019-12-17 12:19:09Z

3

You can use this modified query which will return a row of JSON per row of your table:

SELECT  (
        SELECT  [key_en] AS 'en.key',
                [text_en] AS 'en.text',
                [html_text_en] AS 'en.htmltext',
                [key_fr] AS 'fr.key',
                [text_fr] AS 'fr.text',
                [html_text_fr] AS 'fr.htmltext'
          FOR JSON PATH
        )
  FROM  [MyContent]

answered Dec 17, 2019 at 12:19

Martin

16.5k1 gold badge33 silver badges51 bronze badges

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

1 Comment

roland Over a year ago

Your solution returns an array per row. It should be an array of rows. I have updated my question to reflect that.

Gordon Linoff · Accepted Answer · 2019-12-17 12:20:13Z

1

You can use apply:

SELECT j.j
FROM [MyContent] c CROSS APPLY
     (SELECT c.[key_en] AS 'en.key',
             c.[text_en] AS 'en.text',
             c.[html_text_en] AS 'en.htmltext',
             c.[key_fr] AS 'fr.key',
             c.[text_fr] AS 'fr.text',
             c.[html_text_fr] AS 'fr.htmltext'
      FOR JSON PATH
     ) j(j);

answered Dec 17, 2019 at 12:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

roland Over a year ago

Your solution returns an array per row. It should be an array of rows. I have updated my question to reflect that.

Collectives™ on Stack Overflow

T-SQL - JSON Nested Objects - 1 row vs. multiple rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related