SQLTeam.com | Weblogs | Forums

How to select the data from JSON file


#1

Hi All,

how to select the data from JSON text file.Please check the below json format and my select list is locale,description,domain

[
{
"locale":{
"en-ai":{
"description":"Anguilla - English",
"domain":"com.ai"
},
"es-pr":{
"description":"Puerto Rico - Spanish",
"domain":"com.pr"
},
"nl-sr":{
"description":"Suriname - Dutch",
"domain":"sr"
},
"fr-ml":{
"description":"Mali - French",
"domain":"ml"
},
"en-lb":{
"description":"Lebanon - English",
"domain":"com.lb"
},
"fr-bf":{
"description":"Burkina Faso - French",
"domain":"bf"
},
"en-fm":{
"description":"Federated States of Micronesia - English",
"domain":"fm"
},
"es-mx":{
"description":"Mexico - Spanish",
"domain":"com.mx"
},
"fr-bj":{
"description":"Benin - French",
"domain":"bj"
},
"ru-ee":{
"description":"Estonia - Russian",
"domain":"ee"
},
"en-mv":{
"description":"Maldives - English",
"domain":"mv"
},
"fr-ne":{
"description":"Niger - French",
"domain":"ne"
},
"de-at":{
"description":"Austria - German",
"domain":"at"
},
"en-gg":{
"description":"Guernsey - English",
"domain":"gg"
},
"ar-ae":{
"description":"United Arab Emirates - Arabic",
"domain":"ae"
},
"ru-uz":{
"description":"Uzbekistan - Russian",
"domain":"co.uz"
},
"ru-am":{
"description":"Armenia - Russian",
"domain":"am"
},
"en-sa":{
"description":"Saudia Arabia - English",
"domain":"com.sa"
},
"pt-tl":{
"description":"Timor-Leste - Portuguese",
"domain":"tl"
},
"en-na":{
"description":"Namibia - English",
"domain":"com.na"
},
"ar-bh":{
"description":"Bahrain - Arabic",
"domain":"com.bh"
},
"da-dk":{
"description":"Denmark - Danish",
"domain":"dk"
},
"en-sb":{
"description":"Solomon Islands - English",
"domain":"com.sb"
},
"ro-ro":{
"description":"Romania - Romanian",
"domain":"ro"
},
"af-za":{
"description":"South Africa - Afrikaans",
"domain":"co.za"
},
"en-nf":{
"description":"Norfolk Island - English",
"domain":"com.nf"
},
"de-ro":{
"description":"Romania - German",
"domain":"ro"
},
"fo-dk":{
"description":"Denmark - Faroese",
"domain":"dk"
},
"mo-md":{
"description":"Moldova - Moldovan",
"domain":"md"
},
"ca-es":{
"description":"Spain - Catalan",
"domain":"es"
},
"en-et":{
"description":"Ethiopia - English",
"domain":"com.et"
},
"yo-bj":{
"description":"Benin - Yoruba",
"domain":"bj"
},
"hu-hu":{
"description":"Hungary - Hungarian",
"domain":"hu"
},
"so-dj":{
"description":"Djibouti - Somali",
"domain":"dj"
},
"mt-mt":{
"description":"Malta - Maltese",
"domain":"com.mt"
},
"es-ec":{
"description":"Ecuador - Spanish",
"domain":"com.ec"
},
"bn-in":{
"description":"India - Bengali",
"domain":"co.in"
},
"si-lk":{
"description":"Sri Lanka - Sinhala",
"domain":"lk"
},
"sw-ke":{
"description":"Kenya - Swahili",
"domain":"co.ke"
},
"en-gy":{
"description":"Guyana - English",
"domain":"gy"
},
"fr-be":{
"description":"Belgium - French",
"domain":"be"
},
"en-vg":{
"description":"British Virgin Islands - English",
"domain":"vg"
},
"en-bw":{
"description":"Botswana - English",
"domain":"co.bw"
},
"zh-vn":{
"description":"Vietnam - Taiwanese",
"domain":"com.vn"
},
"en-tz":{
"description":"Tanzania - English",
"domain":"co.tz"
},
"ha-ne":{
"description":"Niger - Hausa",
"domain":"ne"
},
"zu-zw":{
"description":"Zimbabwe - Ndebele",
"domain":"co.zw"
},
"to-to":{
"description":"Tonga - Tongan",
"domain":"to"
},
"kk-kz":{
"description":"Kazakhstan - Kazakh",
"domain":"kz"
},
"es-uy":{
"description":"Uruguay - Spanish",
"domain":"com.uy"
},
"ar-iq":{
"description":"Iraq - Arabic",
"domain":"iq"
},
"zh-tw":{
"description":"Taiwan - Chinese",
"domain":"com.tw"
},
"en-za":{
"description":"South Africa - English",
"domain":"co.za"
},
"de-na":{
"description":"Namibia - German",
"domain":"com.na"
},
"zh-cn-hk":{
"description":"Hong Kong - Chinese (Traditional Han)",
"domain":"com.hk"
},
"rw-rw":{
"description":"Rwanda - Kinyarwanda",
"domain":"rw"
},
"ca-ad":{
"description":"Andorra - Catalan",
"domain":"ad"
},
"en-ly":{
"description":"Libya - English",
"domain":"com.ly"
},
"sq-al":{
"description":"Albania - Albanian",
"domain":"al"
},
"en-il":{
"description":"Israel - English",
"domain":"co.il"
},
"en-ki":{
"description":"Kiribati - English",
"domain":"ki"
},
"fr-lb":{
"description":"Lebanon - French",
"domain":"com.lb"
},
"fr-bi":{
"description":"Burundi - French",
"domain":"bi"
},
"en-us":{
"description":"United States - English",
"domain":"com"
},
"fr-mu":{
"description":"Mauritius - French",
"domain":"mu"
},
"en-sc":{
"description":"Seychelles - English",
"domain":"sc"
},
"zh-hk-hk":{
"description":"Hong Kong - Chinese (Simplified Han)",
"domain":"com.hk"
},
"en-pa":{
"description":"Panama - English",
"domain":"com.pa"
},
"en-ca":{
"description":"Canada - English",
"domain":"ca"
},
"ka-ge":{
"description":"Georgia - Kartuli",
"domain":"ge"
},
"pt-gi":{
"description":"Gibraltar - Portuguese",
"domain":"com.gi"
},
"fr-rw":{
"description":"Rwanda - French",
"domain":"rw"
},
"ta-in":{
"description":"India - Tamil",
"domain":"co.in"
},
"en-qa":{
"description":"Qatar - English",
"domain":"com.qa"
},
"es-gt":{
"description":"Guatemala - Spanish",
"domain":"com.gt"
},
"de-li":{
"description":"Liechtenstein - German",
"domain":"li"
},
"fr-vn":{
"description":"Vietnam - French",
"domain":"com.vn"
},
"km-kh":{
"description":"Cambodia - Kmher",
"domain":"com.kh"
},
"ka-in":{
"description":"India - Kannada",
"domain":"co.in"
},
"ar-lb":{
"description":"Lebanon - Arabic",
"domain":"com.lb"
},
"en-cr":{
"description":"Costa Rica - English",
"domain":"co.cr"
},
"qu-bo":{
"description":"Bolivia - Quechua",
"domain":"com.bo"
},
"es-ve":{
"description":"Venezuela - Spanish",
"domain":"co.ve"
},
"es-ni":{
"description":"Nicaragua - Spanish",
"domain":"com.ni"
},
"en-pr":{
"description":"Puerto Rico - English",
"domain":"com.pr"
},
"ar-td":{
"description":"Chad - Arabic",
"domain":"td"
},
"fr-cf":{
"description":"Central African Republic - French",
"domain":"cf"
},
"en-tk":{
"description":"Tokelau - English",
"domain":"tk"
},
"en-ni":{
"description":"Nicaragua - English",
"domain":"com.ni"
},
"sw-bi":{
"description":"Burundi - Swahili",
"domain":"bi"
},
"es-cr":{
"description":"Costa Rica - Spanish",
"domain":"co.cr"
},
"mg-mg":{
"description":"Madagascar - Malagasy",
"domain":"mg"
},
"bn-bd":{
"description":"Bangladesh - Bengali",
"domain":"com.bd"
},
"en-bz":{
"description":"Belize - English",
"domain":"com.bz"
},
"ar-sa":{
"description":"Saudia Arabia - Arabic",
"domain":"com.sa"
},
"en-gm":{
"description":"Gambia - English",
"domain":"gm"
},
"lo-la":{
"description":"Laos - Lao",
"domain":"la"
},
"yo-ng":{
"description":"Nigeria - Yoruba",
"domain":"com.ng"
},
"zu-za":{
"description":"South Africa - IsiZulu",
"domain":"co.za"
},
"en-gh":{
"description":"Ghana - English",
"domain":"com.gh"
},
"en-tt":{
"description":"Trinidad and Tobago - English",
"domain":"tt"
},
"st-ls":{
"description":"Lesotho - Sesotho",
"domain":"co.ls"
},
"it-gi":{
"description":"Gibraltar - Italian",
"domain":"com.gi"
},
"en-so":{
"description":"Somalia - English",
"domain":"so"
},
"fr-ca":{
"description":"Canada - French",
"domain":"ca"
},
"en-bt":{
"description":"Bhutan - English",
"domain":"bt"
},
"de-be":{
"description":"Belgium - German",
"domain":"be"
},
"fr-lu":{
"description":"Luxembourg - French",
"domain":"lu"
},
"fr-mg":{
"description":"Madagascar - French",
"domain":"mg"
},
"ru-kz":{
"description":"Kazakhstan - Russian",
"domain":"kz"
},
"it-sm":{
"description":"San Marino - Italian",
"domain":"sm"
},
"en-hk":{
"description":"Hong Kong - English",
"domain":"com.hk"
},
"en-je":{
"description":"Jersey - English",
"domain":"je"
},
"en-vn":{
"description":"Vietnam - English",
"domain":"com.vn"
},
"hu-ro":{
"description":"Romania - Hungarian",
"domain":"ro"
},
"xh-za":{
"description":"South Africa - IsiXhosa",
"domain":"co.za"
},
"en-zw":{
"description":"Zimbabwe - English",
"domain":"co.zw"
},
"en-kh":{
"description":"Cambodia - English",
"domain":"com.kh"
},
"lv-lv":{
"description":"Latvia - Latvian",
"domain":"lv"
},
"bs-me":{
"description":"Montenegro - Croatian",
"domain":"me"
},
"ru-kg":{
"description":"Kurgyzstan - Russian",
"domain":"kg"
},
"fr-ma":{
"description":"Morocco - French",
"domain":"co.ma"
},
"ar-tn":{
"description":"Tunisia - Arabic",
"domain":"tn"
},
"en-au":{
"description":"Australia - English",
"domain":"com.au"
},
"sr-me-me":{
"description":"Montenegro - Montenegrin",
"domain":"me"
},
"st-za":{
"description":"South Africa - Sesotho",
"domain":"co.za"
},
"lt-lv":{
"description":"Latvia - Lithuanian",
"domain":"lv"
},
"am-et":{
"description":"Ethiopia - Amharic",
"domain":"com.et"
},
"en-pn":{
"description":"Pitcairn Island - English",
"domain":"pn"
},
"es-hn":{
"description":"Honduras - Spanish",
"domain":"hn"
},
"mfe-mu":{
"description":"Mauritius - Mauritian Creole",
"domain":"mu"
},
"es-es":{
"description":"Spain - Spanish",
"domain":"es"
},
"en-ke":{
"description":"Kenya - English",
"domain":"co.ke"
},
"mi-nz":{
"description":"New Zealand - Maori",
"domain":"co.nz"
},
"ru-md":{
"description":"Moldova - Russian",
"domain":"md"
},
"nl-be":{
"description":"Belgium - Dutch",
"domain":"be"
},
"ko-kr":{
"description":"South Korea - Korean",
"domain":"co.kr"
},
"en-mu":{
"description":"Mauritius - English",
"domain":"mu"
},
"en-ae":{
"description":"United Arab Emirates - English",
"domain":"ae"
},
"ar-kw":{
"description":"Kuwait - Arabic",
"domain":"com.kw"
},
"ms-my":{
"description":"Malaysia - Malay",
"domain":"com.my"
},
"ta-sg":{
"description":"Singapore - Tamil",
"domain":"com.sg"
},
"ar-ps":{
"description":"Palestinian territories - Arabic",
"domain":"ps"
},
"en-by":{
"description":"Belarus - English",
"domain":"by"
},
"en-in":{
"description":"India - English",
"domain":"co.in"
}
}
}
]


#2
SELECT 
	locale = [Key],
    [description],
    [domain]
FROM 
	OPENJSON(@YourJasonString, '$.locale') a
    CROSS APPLY
    OPENJSON(a.Value)
    WITH
    (
        [description] NVARCHAR(255),
        [domain] NVARCHAR(255)
    );

#3

Thank You......