Returning a basic JSON string array in SQL Server

Question

I need to create a basic string array from results - returned in JSON format from SQL Server.

An example JSON string I would want is:

[
  {
    "person": {
        "name":"Jane Bloggs",
        "previousSurnames": [
            "Bloggy",
            "Jones"
        ],
        "Address":"I Live Here"
  }
]

It is the "previousSurnames" I wish to retrieve as JSON but without any preceding labels... just a list of strings.

When I try the conventional way, it always puts the db field as the identifier (along with some extra curly braces which I also don't want!)...

[
  {
    "person": {
        "name":"Jane Bloggs",
        "previousSurnames": [
            {"surname":"Bloggy"},
            {"surname":"Jones"}
        ],
        "Address":"I Live Here"
  }
]

SQL Server must be able to do this as it recognises a simple string array as a correct JSON string e.g.

select isjson('["Bloggy","Jones"]')

returns 1 (Valid)

Help please...

not clear, do you have a table and you want to parse it as json? Or you are trying to get a defined objects list (and parse it as json) from a json field? — B3S, Jun 08 '18 at 15:17
Possible duplicate of https://stackoverflow.com/questions/37708638/sql-to-json-array-of-objects-to-array-of-values-in-sql-2016 — David Browne - Microsoft, Jun 08 '18 at 15:37
SQL Server 2016. Sorry @B3S - yes I am retrieving the details from DB tables - on a 1:n between "person" and "previousNames". — Woody, Jun 08 '18 at 19:53

squillman · Answer 1 · 2018-06-08T16:33:02.447

I think what you're asking is that you want to retrieve the value of previousSurnames as a JSON String. If that is the case then one of these should work.

For a single-item array, such as in your example:

DECLARE @json NVARCHAR(MAX) = N'[
  {
    "person": {
        "name":"Jane Bloggs",
        "previousSurnames": ["Bloggy","Jones"],
        "Address":"I Live Here"
    }
  }
]';

SELECT JSON_QUERY(@json, '$[0].person.previousSurnames')

Or, if your source JSON has multiple items in the array this should work:

DECLARE @json NVARCHAR(MAX) = N'[
  {
    "person": {
        "name":"Jane Bloggs",
        "previousSurnames": ["Bloggy","Jones"],
        "Address":"I Live Here"
    }
  },
  {
    "person": {
        "name":"Jane Bloggs 2",
        "previousSurnames": ["Bloggy2","Jones2"],
        "Address":"I Live Here"
    }
  }
]';
SELECT JSON_QUERY(value, '$.person.previousSurnames')
FROM OPENJSON(@json)

Returning a basic JSON string array in SQL Server

1 Answers1