The solution for anyone, who might run into the same issue is to place double quotes on the element with the dot, so it works perfect for me now, thank...

I can't keep on giving answers on moving goal posts. I have shown how you would do this, I will leave it to you to implement.

JSON_VALUE(v.value, '$.fields.System.State.newValue') will not...

I have added more elements to "fields" complex one and seems I can receive the results without CROSS APPLY, but not those with the dot

DECLARE @v_Json NVARCHAR(MAX)...

Providing I have implemented your suggestion correctly as per my code below, I receive no value for System.State either

DECLARE @v_Json NVARCHAR(MAX) = N'{
"count": 56,
"value": [{
"id": 1,
"workItemId": 1234,
"fields":...

Sorry for Oracle, yes, Execution Plan. I am not suggesting anything, yet asking for your opinion.

CROSS APPLY will affect show plan significantly on large JSON files.

I think CROSS APPLY in this case is overkilling, as there is only one "fields" complex element per id. What do you think?

Following your correct comment, below is fully working SQL example for your perusal:

DECLARE @v_Json NVARCHAR(MAX) = N'{
"count": 56,
"value": [{
"id": 1,
"workItemId": 1234,
"fields": {
"System.Id": {
"newValue": 1234
},
"System.State": {
"newValue": "New"
}
}
},
{
"id": 2,
"workItemId":...

The JSON you've given us isn't even valid. If we take the time to format it properly, there's plenty of problems.

{
    "count": 56,
...

Hi,My command is  :  EXEC Master.dbo.xp_DirTree 'D:\Backups\',1,1And I have one file in the directory, but the out is  : (0 rows affected)I am running this on SQL Server 2017...

Thank you, Jeff!

Since very few people ever come back to tell us, I'll make you a deal... ...

Thank you All!

Steve, apologies - I had no intention to offend you. I have looked into fnTally / the link you have kindly provided.

Much obliged.

Thank you, Steve

Sadly none of your examples works for me from the Studio - getting various error messages.

I would expect to see Bertha and Betty (doesn't matter the order nor if it is all lower / UPPER case).

Thank you.