How to use SQL Server 2016 to select JSON array where

Question

{
    "NameDetailInfo": [{
        "cName": "Tony",
        "sex": "M"
    }, {
        "cName": "Lucy",
        "sex": "M"
    }, {
        "cName": "Lily",
        "sex": "M"
    }, {
        "cName": "Ben",
        "sex": "M"
    }, {
        "cName": "Daa",
        "sex": "F"
    }, {
        "cName": "Kode",
        "sex": "F"
    }, {
        "cName": "Andy",
        "sex": "F"
    }, {
        "cName": "Koya",
         "sex": "F"
    },{
        "cName": "Kay",
        "sex": "M"
    }]
}

I want select this array cName.

select *
from CD_Name_List
where JSON_VALUE(CD_Name_List.MemberJSON,'$.NameDetailInfo[0].cName') = 'Kay'

This just select NameDetailInfo[0], but I want select all array.

Charlieface · Accepted Answer · 2021-09-17 00:14:59Z

4

You can use OPENJSON to break out the array

SELECT j.cName, j.sex
FROM CD_Name_List nl
CROSS APPLY OPENJSON (nl.MemberJSON, '$.NameDetailInfo')
  WITH (cName nvarchar(100), sex char(1)) j
WHERE j.cName = 'Kay';

Or, if you meant that you want to select the whole array if such an element exists, you can use OPENJSON in a subquery

SELECT nl.*
FROM CD_Name_List nl
WHERE EXISTS (SELECT 1
    FROM OPENJSON (nl.MemberJSON, '$.NameDetailInfo')
      WITH (cName nvarchar(100)) j
    WHERE j.cName = 'Kay'
);

db<>fiddle

answered Sep 17, 2021 at 0:14

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use SQL Server 2016 to select JSON array where

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related