JSON_VALUE with square-bracket terminated JSON object

Question

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?

Martin Smith · Accepted Answer · 2020-03-17 01:40:04Z

3

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

answered Mar 17, 2020 at 1:40

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

Collectives™ on Stack Overflow

JSON_VALUE with square-bracket terminated JSON object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related