SQL Server - return count of json array items in sql server column across multiple rows-CodePudding

Using SQL Server 2019, I have data in a table with the following columns:

id, alias, value

The value column contains json in this format:

[
    {
        "num": 1,
        "description": "test 1"
    },
    {
        "num": 2,
        "description": "test 2"
    },
        {
        "num": 3,
        "description": "test 2"
    }
]

I want to get a count of "num" items within the json for each row in my SQL table.

For example query result should look like

id   CountOfNumFromValueColumn
------------------------------
 1             3

Update - The SQL below works with the above json and gives results in the above format

SELECT id, count(Num) AS CountOfNumFromValueColumn
FROM MyTable
CROSS APPLY OPENJSON ([value], N'$')
  WITH (
    num int)
WHERE ISJSON([value]) > 0 
GROUP BY id

CodePudding user response：

Hello pls try with some like that.

create function dbo.ufn_counter_rec(
@str varchar(max),
@pattern varchar(100))
returns int
as
begin

declare @tbl table(mystring varchar(max));
declare @result int;

insert into @tbl values(@str);

with T as (
select 0 as row, charindex(@pattern, mystring) pos, mystring from @tbl
union all
select cast(pos as int)   1, charindex(@pattern, mystring, pos   1), mystring
from T
where pos > 0
)

select @result = count(*) from T where pos > 0;

return @result

end

GO

select dbo.ufn_counter_rec('[
{
    "num": 1,
    "description": "test 1"
},
{
    "num": 2,
    "description": "test 2"
},
    {
    "num": 3,
    "description": "test 2"
}
]', 'num')

CodePudding user response：

Original question has now been updated with correct answer