How to Write A Json Query In SQL Server?

Question

Say I have this json in a SQL Column called MyJson in a table called StoreTeams

{
    MyTeams: [
        {
            id: 1
        },
        {
            id: 2
        },
        {
            id: 3
        }
    ]
}

I want to take all these id's and then do a inner join against another table.

User Table

- id <pk>
- firstName
- lastName

I am not sure how I would do this, I would be probably running this code via ado.net.

Answer 1

You can use openjson(). You don't specify the exact result you want, but the logic is:

select *
from mytable t
cross apply openjson(t.myjson, '$.MyTeams') with (id int '$.id') as x
inner join users u on u.id = x.id