How do you query property values with SQL Server JSON without knowing the parent object keys?

Question

Given the following JSON in SQL Server, how do you get the "Foo" values without knowing the parent keys ("John" and "Jane")?

declare @json nvarchar(max) = '{
    "John" : {
        "favoriteMovie": "Star Wars",
        "Foo": 42
    },
    "Jane" : {
        "favoriteMovie": "Caddyshack",
        "Foo": 66
    }
}'

Answer 1

In SQL Server 2016 or later, you can use OPENJSON and APPLY for the number of levels in your hierarchy.

select b.[key], b.[value]
from openjson(@json) a
    cross apply openjson(a.[value]) b
where b.[key] like N'Foo';

Results:

key value
Foo 42
Foo 66