Access Array record and normal record from JSON

Question

I am new to handling json records. I have a json record as follows:

{"responseobj":{"ResponseCode":"0","ResponseDesc":"Success"},
    "objDropdown":[{"Master_id":0,"ID":"en","Value":"English","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"fr","Value":"français","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"it","Value":"italiano","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"pt","Value":"Português","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"es","Value":"español","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"de","Value":"Deutsch","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"nl","Value":"Nederlands","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
      ]}'

Please teach me how to get a record from this.

Answer 1

You can do that with OpenJSON() - SQL server 2016 and up:

DECLARE @json VARCHAR(4000);

SET @json
    = '{"responseobj":{"ResponseCode":"0","ResponseDesc":"Success"},
    "objDropdown":[{"Master_id":0,"ID":"en","Value":"English","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"fr","Value":"français","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"it","Value":"italiano","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"pt","Value":"Português","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"es","Value":"español","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"de","Value":"Deutsch","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
        ,{"Master_id":0,"ID":"nl","Value":"Nederlands","Selected":false,"IsActive":false,"Description":null,"TopupId":0,"TopupAmount":0}
      ]}';

SELECT *
FROM OPENJSON(@json, '$.objDropdown')
    WITH
    (
        Master_id INT '$.Master_id',
        ID VARCHAR(5) '$.ID',
        [Value] NVARCHAR(50) '$.Value',
        Selected BIT '$.Selected',
        IsActive BIT '$.IsActive',
        [Description] NVARCHAR(4000) '$.Description',
        TopupId INT '$.TopupId',
        TopupAmount INT '$.TopupAmount'
    );

EDIT: Getting responseobj is even simpler:

SELECT *
FROM OPENJSON(@json, '$.responseobj');