October 30, 2017 at 12:15 am
Hi,
I am trying to find a way to update a SQL nvarchar(max) column used to contain JSON data. So far, nothing I have tried, will successfully update a 2nd-level/nested array. I have found ways to retrieve the information at that level, but not to update it.
This is a pseudo-example of the expected JSON content structure. There are potentially multiple comment entries, each containing an array of "likes". The "likes" array is what I am not finding a way to update. The individual objects (personID + firstName + lastName + createdDate) will not be modified, once added; they will be added as an entire object, or removed as an entire object.
{"comments":[
{"percentComplete":36,"commentText":"my comment","modifiedDateTime":"2017-10-26 20:23:42.786",
"likes": [ {"personID": 8, "firstName": "George", "lastName": "Smith", "createdDate": "11/11/2017"},
{"personID": 1, "firstName": "Jane", "lastName": "Doe", " createdDate": "12/12/2017"} ] },
{"percentComplete":50,"commentText":"final update","modifiedDateTime":"2017-11-26 20:23:42.786 ",
"likes": [ {"personID": 11, "firstName": "Jonathan", "lastName": "David", "createdDate": "11/14/2017"},
{"personID": 14, "firstName": "Salvador", "lastName": "Velez", " createdDate": "12/15/2017"} ] }
... ]}
Thanks,
Randy
October 30, 2017 at 7:53 am
To be honest, storing data in JSON format and then expecting to update it using the abilities of an RDBMS, is not particularly practical. JSON is built for data exchange, not data storage. While you can update data stored in JSON format, the question is, why would you want to? I don't see a way to make a compelling case for just storing data in JSON format, when keeping the data in columns with proper data types makes so much more sense from a data storage point of view. If you think you've got a good use case, please detail it out. It's likely to at least foster some form of debate.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply