CODEWITHSUNDEEP
jsonsql-serversql-server-json
Randy Minder
Randy Minder

Reputation: 48392

Retrieving a specific JSON array from a JSON array of values

I have a SQL table that contains a column holding JSON data. One of the JSON values looks as follows:

{
    "_id": "5a450f038104ca3cb0ff74b5",
    "index": 3,
    "guid": "20d807c5-bddc-44b9-97fe-fd18af1b6066",
    "isActive": false,
    "balance": "$2,832.38",
    "picture": "http://placehold.it/32x32",
    "age": 23,
    "eyeColor": "brown",
    "firstname": "Genevieve",
    "lastname": "Green",
    "gender": "female",
    "company": "PROFLEX",
    "email": "[email protected]",
    "phone": "+1 (919) 464-2866",
    "address": "107 Clermont Avenue, Rew, California, 4298",
    "about": "Magna pariatur ut enim nulla pariatur ad Lorem amet. Proident nulla exercitation id Lorem commodo minim cillum irure exercitation labore nostrud nostrud sint. Voluptate commodo ea commodo quis Lorem laborum culpa voluptate enim nulla enim duis.\r\n",
    "registered": "2016-02-16T09:51:25 +05:00",
    "latitude": -16.492643,
    "longitude": -71.782118,
    "tags": [
      "in",
      "non",
      "eiusmod",
      "labore",
      "dolor",
      "laboris",
      "ullamco"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Mccoy Berg",
        "interests": [
          "Music",
          "Birding",
          "Chess"
        ]
      },
      {
        "id": 1,
        "name": "Chase Mcfadden",
        "interests": [
          "Software",
          "Chess",
          "History"
        ]
      },
      {
        "id": 2,
        "name": "Michele Dodson",
        "interests": [
          "Football",
          "Birding",
          "Movies"
        ]
      }
    ],
    "greeting": "Hello, Genevieve! You have 2 unread messages.",
    "favoriteFruit": "strawberry"
  }

I can execute a query that retrieves the first and last name and all the friends as follows:

SELECT
  JSON_VALUE(JsonValue, '$.firstname') as FirstName,
  JSON_VALUE(JsonValue, '$.lastname') as LastName,
  JSON_QUERY(JsonValue, '$.friends') as FriendsList,
From <MyTable>
Where JSON_VALUE(JsonValue,'$.lastname') = 'Green'

The query, as written, returns a JSON string for FriendsList that looks like this:

[  
   {  
      "id":0,
      "name":"Mccoy Berg",
      "interests":[  
         "Music",
         "Birding",
         "Chess"
      ]
   },
   {  
      "id":1,
      "name":"Chase Mcfadden",
      "interests":[  
         "Software",
         "Chess",
         "History"
      ]
   },
   {  
      "id":2,
      "name":"Michele Dodson",
      "interests":[  
         "Football",
         "Birding",
         "Movies"
      ]
   }
]

What I would actually like is just an array of friends names, something like this:

["Mccoy Berg", "Chase Mcfadden", ...]

I'm sure this is possible but my knowledge of JSON is limited.

Upvotes: 1

Views: 127

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

SQL server is kind of funny when it comes to creating arrays in the format you are expecting. By default, it always creates JSON arrays as key:value pairs. You can work around this by using STUFF() and FOR XML when working with JSON.

You first create a subquery that returns only the names, then you can STUFF those names into the FriendsList field, like so:

SELECT
FirstName    = JSON_VALUE(JsonValue, '$.firstname')
,LastName    = JSON_VALUE(JsonValue, '$.lastname')
,FriendsList = '[' + STUFF(
                        (SELECT ',' + '"' + [name] + '"'
                            FROM OPENJSON(JsonValue,'$.friends')
                            WITH ( [name] NVARCHAR(100) '$.name') 
                        FOR XML PATH (''))
                        , 1, 1, '') 
            + ']'
FROM <MyTable>

Upvotes: 3

Related Questions