JSON_VALUE with square-bracket terminated JSON object

user_4017932 0
Viewed 1032

I have a table with a JSON field terminated by the square brackets. It's in valid JSON format validated by https://jsonformatter.curiousconcept.com/

[
   {
      "billId":"1111",
      "memberId":"2222",
      "patientId":"3333",
      "details":[
         {
            "itemNumber":"A.111",
            "quantity":1,
            "unitPrice":1.11,
            "priceList":null,
            "location":"2",
            "uom":"each"
         },
         {
            "itemNumber":"A.11",
            "quantity":1,
            "unitPrice":1.11,
            "priceList":null,
            "location":"2",
            "uom":"Each"
         }
      ]
   }
]

The JSON_VALUE function seems to have a problem parsing this object refusing to cooperate with me until I remove the terminating square brackets using something quite crude like right(LEFT(JsonBody,len(JsonBody)-1),len(JsonBody)-2) as Json and than it's happy to work returning what I expect from a command like SELECT JSON_VALUE (Json,'$.billId').

What would be a better way of handling this situation - is there a switch, an option for the JSON_VALUE (which I have overlooked) which allows it to handle JSON objects formatted in the manner above or some other more elegant way of dealing with this situation?

sql-server-2016 sql-server
1 Answers

The square brackets denote an array with a single element so you need to use the array access syntax to select the first (and only) element.

'$[0].billId'

Demo

user_732260
Related