Count the size of an array in another column?-CodePudding

I'm trying to update "Count_Column" in "My_Table" to get the count of the array size in Array_Column

UPDATE TABLE [dbo1].[My_Table]
SET [Count_Column] as COUNT(Array_Column.test);

Array_Column in a column that has a JSON array in it

CodePudding user response：

It's unclear why you want to store this information a second time, ideally you would just query it when needed.

Be that as it may, you need to break out the array using OPENJSON

UPDATE dbo1.My_Table
SET Count_Column = (
    SELECT COUNT(*)
    FROM OPENJSON(My_Table.Array_Column, '$.test')
);

This assumes that the array is located in a property called test, for example:

{
  "test" : [
    1,
    2
  ]
}

would return 2