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
– B3S, Commented Jun 8, 2018 at 15:17
Possible duplicate of stackoverflow.com/questions/37708638/… — David Browne - Microsoft
– David Browne - Microsoft, Commented Jun 8, 2018 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
– Woody, Commented Jun 8, 2018 at 19:53

squillman · Accepted Answer · 2018-06-08 16:33:02Z

0

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)

edited Jun 8, 2018 at 16:33

answered Jun 8, 2018 at 16:24

squillman

13.7k3 gold badges45 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Returning a basic JSON string array in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related