Determine the type of a JSON-object in MSSQL

JsonType.sql

;WITH CTE AS 
(
	          SELECT NULL AS CLV_Value 
	UNION ALL SELECT '123' AS CLV_Value 
	UNION ALL SELECT '[1,2,3]' AS CLV_Value 
	UNION ALL SELECT '{"0": 1234}'
	UNION ALL SELECT '{
	  "json": "[]",
	  "svg": null,
	  "jpg": null,
	  "png": null
	}' AS CLV_Value 
)
SELECT 
	 CLV_Value 
	,CASE 
        WHEN ISJSON(CLV_Value) = 1 THEN 
			CASE
				WHEN JSON_VALUE(CLV_Value, '$[0]') IS NOT NULL THEN 'array' 
				WHEN JSON_QUERY(CLV_Value, '$') IS NOT NULL THEN 'object' 
			END
		ELSE 'not json' 
	END AS json_type 	
FROM CTE 





-- ALTER DATABASE <YOUR_DB_NAME> SET COMPATIBILITY_LEVEL = 160


;WITH CTE AS 
(
	          SELECT NULL AS CLV_Value 
	UNION ALL SELECT '123' AS CLV_Value 
	UNION ALL SELECT 'abc' AS CLV_Value 
	UNION ALL SELECT '[1,2,3]' AS CLV_Value 
	UNION ALL SELECT '{"0": 1234}'
	
	UNION ALL SELECT '{
	  "json": "[]",
	  "svg": null,
	  "jpg": null,
	  "png": null
	}' AS CLV_Value 


	UNION ALL SELECT '[{
 "file":"data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,UEsDBBQABgAIAAAAIQBBN4LPbgEAAAQFAAATAAgCW0NvbnRlbnRfVHlwZXNdLnhtbCCiBAIooAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACsVMluwjAQvVfqP0S+Vomhh6qqCBy6HFsk6AeYeJJYJLblGSj8fSdmUVWxCMElUWzPWybzPBit2iZZQkDjbC76WU8kYAunja1y8T39SJ9FgqSsVo2zkIs1oBgN7+8G07UHTLjaYi5qIv8iJRY1tAoz58HyTulCq4g/QyW9KuaqAvnY6z3JwlkCSyl1GGI4eINSLRpK3le8vFEyM1Ykr5tzHVUulPeNKRSxULm0+h9J6srSFKBdsWgZOkMfQGmsAahtMh8MM4YJELExFPIgZ4AGLyPdusq4MgrD2nh8YOtHGLqd4662dV/8O4LRkIxVoE/Vsne5auSPC/OZc/PsNMilrYktylpl7E73Cf54GGV89W8spPMXgc/oIJ4xkPF5vYQIc4YQad0A3rrtEfQcc60C6Anx9FY3F/AX+5QOjtQ4OI+c2gCXd2EXka469QwEgQzsQ3Jo2PaMHPmr2w7dnaJBH+CW8Q4b/gIAAP//AwBQSwMEFAAGAAgAAAAhALVVMCP0AAAATAIAAAsACAJfcmVscy8ucmVscyCiBAIooAACAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACskk1PwzAMhu9I/IfI99XdkBBCS3dBSLshVH6ASdwPtY2jJBvdvyccEFQagwNHf71+/Mrb3TyN6sgh9uI0rIsSFDsjtnethpf6cXUHKiZylkZxrOHEEXbV9dX2mUdKeSh2vY8qq7iooUvJ3yNG0/FEsRDPLlcaCROlHIYWPZmBWsZNWd5i+K4B1UJT7a2GsLc3oOqTz5t/15am6Q0/iDlM7NKZFchzYmfZrnzIbCH1+RpVU2g5abBinnI6InlfZGzA80SbvxP9fC1OnMhSIjQS+DLPR8cloPV/WrQ08cudecQ3CcOryPDJgosfqN4BAAD//wMAUEsDBBQABgAIAAAAIQDJiEqruAMAAD4JAAAPAAAAeGwvd29ya2Jvb2sueG1srFVtb6M4EP5+0v0HDvUrweYtgJKsIBBdpXZVpbn2TqoUueAUq4BzxjSpqv3vOyYhTTenU657UWLH9vD4mZlnhtGXbVVqL1Q0jNdjHQ+QrtE64zmrn8b6H4uZ4etaI0mdk5LXdKy/0kb/Mvn1l9GGi+dHzp81AKibsV5IuQ5Ns8kKWpFmwNe0hpMVFxWRsBRPZrMWlORNQamsStNCyDMrwmp9hxCKczD4asUymvCsrWgtdyCClkQC/aZg66ZHq7Jz4Cointu1kfFqDRCPrGTytQPVtSoLL59qLshjCW5vsattBXw9+GEEg9XfBEcnV1UsE7zhKzkAaHNH+sR/jEyMP4RgexqD85AcU9AXpnJ4YCW8T7LyDljeOxhGP42GQVqdVkII3ifR3AM3S5+MVqykdzvpamS9/koqlalS10rSyDRnkuZjfQhLvqEfNkS7jltWwqnlYyvQzclBzjdCy+mKtKVcgJB7eDBElo2QsgRhRKWkoiaSTnktQYd7v35Wcx32tOCgcG1O/26ZoFBYoC/wFUaSheSxuSGy0FpRjvUkfGgkcK0HMLEnKh4SvqlLDjX2cCROcloJ/0GeJFM+m+D0jtju/48BAH4i7CV4I4UG/y+TK0jDLXmBpEDq833NXkLUsb2sMxHi5RvyZgilrmVEqT00HN/3DD8IfMOKoyiO3NiJXecbOCO8MOOklcU+3wp6rDuQ3JOja7LtTzAKW5a/03hD+4+h5h+G/uybclh1tjtGN827MtRS296zOucb0IMbDB1de+3XBkaBrm2603uWywJMPBvhfu93yFABlLHnWqoQhKWojfU3ZxrZycx2jSBwZ4bjDm0jQDPbcKfKf8sZosTqKJlHnLomCty6Was74S/IIy1LCjd2u12cdU2E6hZxmeMuj/2DoHFW01yVDMAcrfZgy21ZV4MbwWq5jKBtqyLKSHmrmrdCRvqkv++3i+gChxfphWOPzCMkUMrHW+D5DMpLTR25AKNd7dGtvGrkZAQzKJtBULCDoiEKHAOlEBnHDyzDd2zLmDqJlbrDNEljV8lCvXrC/6MBdwUW9u80xbIgQi4EyZ7hTTinq5g0oONdEIHnMdnY9WNkA0VnhiGDOEBGHHuO4aq0DnEyTd3ZO1nl/uqT7c83u6cpkS20BtUVunWoxtl+97C52m3sE/qh5MN5otSwf/rfDG/B+5KeaTy7O9Nw+vV6cX2m7VW6WN7PzjWOruMkOt8+ms+jvxbpn/0V5j8G1OwSrsZOpmYvk8l3AAAA//8DAFBLAwQUAAYACAAAACEAgT6Ul/MAAAC6AgAAGgAIAXhsL19yZWxzL3dvcmtib29rLnhtbC5yZWxzIKIEASigAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAArFJNS8QwEL0L/ocwd5t2FRHZdC8i7FXrDwjJtCnbJiEzfvTfGyq6XVjWSy8Db4Z5783Hdvc1DuIDE/XBK6iKEgR6E2zvOwVvzfPNAwhi7a0egkcFExLs6uur7QsOmnMTuT6SyCyeFDjm+CglGYejpiJE9LnShjRqzjB1Mmpz0B3KTVney7TkgPqEU+ytgrS3tyCaKWbl/7lD2/YGn4J5H9HzGQlJPA15ANHo1CEr+MFF9gjyvPxmTXnOa8Gj+gzlHKtLHqo1PXyGdCCHyEcffymSc+WimbtV7+F0QvvKKb/b8izL9O9m5MnH1d8AAAD//wMAUEsDBBQABgAIAAAAIQC3GpD2lgYAACwfAAAYAAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1snJPbjpswEIbvK/UdLN8TICEsRCGrNjTq3lSrbg/XjhkSKzamtnNq1XfvmITsSqnaaCXAB/x/8489nt4flCQ7MFbopqDxIKIEGq4r0awK+vXLIsgosY41FZO6gYIewdL72ds30702G7sGcAQJjS3o2rl2EoaWr0ExO9AtNPin1kYxh0OzCm1rgFWdSMlwGEVpqJho6IkwMbcwdF0LDqXmWwWNO0EMSObQv12L1vY0xW/BKWY22zbgWrWIWAop3LGDUqL45GHVaMOWEvM+xAnj5GDwGeI76sN081eRlOBGW127AZLDk+fr9PMwDxm/kK7zvwkTJ6GBnfAH+Iwavs5SPL6whs+w0Sth6QXmt8tMtqIq6K/kLsqTOPsQ5FGSB0k+T4MsK6NgfldmaTmKk2wU/aazaSXwhH1WxEBd0HfxpExSGs6mXQF9E7C3L/rEseUTSOAOMEhMyQ4XFLRlK3iPVbd59HsEe0p+aq2eOPNnmmKxX4affKFKlEY464t7qfXGR3lAXoR+bEf3fhh3YgdzkLi8jHG5/dFZ9H30F14Mvuz3ZhfdhXg0ZMkszLX8Liq39mEpqaBmW+k+6/1HEKu1w9lk4LfO196kOpZgORY92hkMxz4S1xKx+CVK+NuLRcsOXbs/U1F/N6aEb63Tqg91lp5EeM6dCNteNP6vCE11Imx70dB7+leg5KzB9qwZZYPUi5Zg3UL4dP8GCLss/wAAAP//AAAA//+UmV1u2zAQhK9i+AC1+SM5CWwDdXQRwzXQp7SIjaS9fbkkLe6OugH3LcCMtBNS+2lF728/r9f7dL6fj/v3X5+r98ParVe33+e3W/rrJa5Xf1w8X15+/J2ut8v17X5Yb7/5YX3cX8j7ncx0yXH/cYzDGMb95uO431yqfip6uk268JZdbnZsUsG5qrdUJTOvuoOqRQ+sqpeO1+SYE4Umikjp+v6FIDOP9ASRis4jBYjENQd5J3Fhu1LkTavcn5fMPO8z5C06Lxshr9hUzMvFoOQdLHnJzPLGLeQtOt/W4f8P2mipSmZetT28+fE/FZ1XbQ0g9mZnqUpmXhUW95T0+eGFdXjlmmtLkONOQlSSPlmSkpknhWf6VPSO9n+2VCUzrwpP5inp8/pg43MNsDFxLbSbio10W0vS7OZRYUdOZGgggg4TogOiTFLV4tpoDjiPC5wnwxwXgeC46OHSSapt6eXqml4DDt4DEd8D1dBBBWeCfXbzbUXaV0MHGJyJ2tnNCyO2yaCyQYi+XSq3wIRlB1wekMvV0IEAZyJzdh/Wu8fkMSCaqyERSGUBWWaxbaFcDhO5XUF3S4XorgaeCucAssyptE0yUdoVDNPUlqe0ATFdDXw5Fs3NIzstlgnjDjg+IMeroaN9vYnK2c26aEAqV0NH+3oTX7ObF0a+kkFtXyFq7ettgzQQdFhM0sXQM8CbCOrLPNwaBQlaDV+1L1naZ0UDj/yuMPHVl7G3xUK+VsNX/UuWFkv73DFRNn1wiYlnRMqSQX0vCzHgTChUr8U1sdnD2Dwim6uhp7FN+PUwOY+I32roaWwTYT0MwiMSlgx6Y4vHabFBoge0DTKR1wN5RyRvNXS0fTCRN7sZAEckLxnU97UUF5/I4lLlmzOYeJ3dPC7ymgzq+C5ED0s8SVX5WA0mmmc3j4s0J4OKCSFqr5dgOxSBU5FxcSpSDB0gCCaOZzdfCeR4NXSAIJhInd2s8A5JTQYVBEJ0wJBJqsoXVDCROrt5XCR1NfSAwETqAKTeIanJoINAHGQs1kmo2jqZ+B6A7zvkOxl0EAiCwxMxyUuVU5lg4nt2821FvpNBB4E4P1nEFackStxoeitkN4+Lb4Vq6MBENPE9u3lh5Hs1dGAimkid3bwwkpoMKiaE6PFcSKhBmReiiePZzeMix8nQZl48KBaH18qcHm2n14/j6036sYH/jBBNtM5u+r8WtzFRND7m3fk2m/azyj8AAAD//wAAAP//dI9BTsNADEWvYvkANIG0IajTTdl0UQmpJxgaNzMiHVuOAbWnx4F2iRfW9/8L/7eWxIUsH98UTlxs1wesEewiFLDwlssX6ZS54GKzljjQPuqQywQjnSxg9dAiaB7SXRvLr/vcNlW9bJvub1YI72zG53/CRLEnncMn9B5s9+P29UD2KSBRSA/56tU6hOkYx1ktEVgzFYvmPQMKq2nMhpDcvzpUHF8lB2weu6Zbtb4RnMqhb0HlDC/ZyXXX1zPo4pv1Y0pEtvkBAAD//wMAUEsDBBQABgAIAAAAIQBOxaqmuAcAAAsiAAATAAAAeGwvdGhlbWUvdGhlbWUxLnhtbOxazY8btxW/B8j/QMxd1szoe2E50Kc39u564ZVd5EhJlIZeznBAUrsrFAEK59RLgQJp0UuB3nooigZogAa55I8xYCNN/4g8ckaa4YqKvf5AkmJ3LzPU7z3+5r3H996Qc/eTq5ihCyIk5UnXC+74HiLJjM9psux6TybjSttDUuFkjhlPSNdbE+l9cu/jj+7iAxWRmCCQT+QB7nqRUulBtSpnMIzlHZ6SBH5bcBFjBbdiWZ0LfAl6Y1YNfb9ZjTFNPJTgGNQ+WizojHj3NmpHDHQnSuqBGRNnWinZxc7PA42QazlgAl1g1vVghjm/nJAr5SGGpYIfup5v/rzqvbtVfJALMbVHtiQ3Nn+5XC4wPw/NnGI53U7qj8J2PdjqNwCmdnGjtv7f6jMAPJvBk2ZcyjqDRtNvhzm2BMouHbo7raBm40v6azucg06zH9Yt/QaU6a/vPuO4Mxo2LLwBZfjGDr7nh/1OzcIbUIZv7uDro14rHFl4A4oYTc530c1Wu93M0VvIgrNDJ7zTbPqtYQ4vUBAN2+jSUyx4ovbFWoyfcTEGgAYyrGiC1DolCzyD+O2liks0pDJleO2hFCdcwrAfBgGEXt0Pt//G4viA4JK05gVM5M6Q5oPkTNBUdb0HoNUrQV5+882L51+/eP6fF1988eL5v9ARXUYqU2XJHeJkWZb74e9//N9ff4f++++//fDln9x4Wca/+ufvX3373U+ph6VWmOLln7969fVXL//yh+//8aVDe0/gaRk+oTGR6IRcosc8hgc0prD5k6m4mcQkwtSSwBHodqgeqcgCnqwxc+H6xDbhUwFZxgW8v3pmcT2LxEpRx8wPo9gCHnPO+lw4DfBQz1Wy8GSVLN2Ti1UZ9xjjC9fcA5xYDh6tUkiv1KVyEBGL5inDicJLkhCF9G/8nBDH031GqWXXYzoTXPKFQp9R1MfUaZIJnVqBVAgd0hj8snYRBFdbtjl+ivqcuZ56SC5sJCwLzBzkJ4RZZryPVwrHLpUTHLOywY+wilwkz9ZiVsaNpAJPLwnjaDQnUrpkHgl43pLTH2JIbE63H7N1bCOFoucunUeY8zJyyM8HEY5TJ2eaRGXsp/IcQhSjU65c8GNurxB9D37AyV53P6XEcvfrE8ETSHBlSkWA6F9WwuHL+4Tb63HNFpi4skxPxFZ27QnqjI7+ammF9hEhDF/iOSHoyacOBn2eWjYvSD+IIKscEldgPcB2rOr7hEiCTF+zmyKPqLRC9ows+R4+x+triWeNkxiLfZpPwOtW6E4FLEbHcz5is/My8IRC4wfx4jTKIwk6SsE92qf1NMJW7dL30h2va2H5703WGKzLZzddlyBDbiwDif2NbTPBzJqgCJgJpujIlW5BxHJ/IaLrqhFbOeUW9qIt3ACNkdXvxDR5XfNzgoXglz9P7/PBuh634nfpd/bllcNrXc4+3K+wtxniVXJKoJzsJq7b1ua2tfH+71ubfWv5tqG5bWhuGxrXK9gHaWiKHgbam2Krx2z8xHv3fRaUsTO1ZuRImq0fCa818zEMmj0pszG53QdMI7jUzwMTWLilwEYGCa5+Q1V0FuEU9ocCs+O5lLnqpUQpl7BtZIbNTiq5pttsPq3iYz7PtjvN/pKfmVBiVYz7Ddh4ysZhq0pl6GYrH9T8NtQN26XZat0Q0LI3IVGazCZRc5BobQZfQ0LvnL0fFh0Hi7ZWv3HVjimA2tYr8N6N4G296zXqGSPYkYMefa79lLl6413tnPfq6X3GZOUIgK3FXU93NNe9j6efLgu1N/C0RcI4JQsrm4TxlWnwZARvw3l0lvfdfyrgburrTuFSi542xWY1FDRa7Q/ha51EruUGlpQzBUvQJazxEBadh2Y47XoL2DeGyziF4JH63QuzJRy7zJTIVvzbpJZUSDXEMsosbrJO5p+YKiIQo3HX08+/DQeWmCSSkevA0v2lkgv1gvulkQOv214miwWZqbLfSyPa0tktpPgsWTh/NeJvD9aSfAXuPovml2jKVuIxhhBrtALt3TmVcHwQZK6eUzgP22ayIv6uVaY8+1uHXEU+xiyNcF5Sytk8g5uCsqVj7rY2KN3lzwwG3TXhdKkr7DuX3dfXam25oj52iqJppRVdNt3Z9MNV+RKrooparLLcfT3ndjbJDgLVWSbevfaXqBWTWdQ04908rJN2PmpTe48dQan6NPfYbVsknJZ429IPctejVleITWNpAt8cmZfPtvn0GSSPIZwirlh22s0SuDOtZXoqjG+nfL7OL5nMEk3mc92UZqn8MVkgOr/qeqGrc8wPj/NugCWANj0vrLCtoLPbswV1sctFswW7Fc7a2Gv9qi28ldgcs26Fzdaii7a62pyo617dzKwdlj21ScPGUnC1a0U4/hcYWufsMDfLvZBnrlTeacMVWgna9X7rN3r1QdgYVPx2Y1Sp1+p+pd3o1Sq9RqMWjBqBP+yHnwM9FcVBI/vmYQynQWydf/lgxne+fog3B153ZjyucvN1Q9V433z9EISOrx/QRH/k4IEjgVY4CuphLxxUBsOgWamHw2al3ar1KoOwOQx7ULSb497nHrow4KA/HI7HjbDSHACu7vcalV6/Nqg026N+OA5G9aEP4Lz8XMFbjM65uS3g0vC69yMAAAD//wMAUEsDBBQABgAIAAAAIQDV+eoUggMAAL0LAAANAAAAeGwvc3R5bGVzLnhtbMRWW2/bNhR+H9D/QPBd1sWSLBmWizqOgAJdUSApsFdaohyiFClQdCp32H/vISXZSrpkTtZuehF5SH7n47nxrN52NUf3VLVMigz7Mw8jKgpZMrHP8Ofb3EkwajURJeFS0AwfaYvfrt/8tmr1kdObO0o1AgjRZvhO62bpum1xR2vSzmRDBaxUUtVEw1Tt3bZRlJStOVRzN/C82K0JE7hHWNbFJSA1UV8OjVPIuiGa7Rhn+mixMKqL5fu9kIrsOFDt/JAUqPNjFaBOjUqs9Ac9NSuUbGWlZ4DryqpiBf2RbuqmLinOSID8OiQ/cr3gwd079Uqk0FX0nhn34fWqkkK3qJAHoTMcAFFjguUXIb+K3CyBh4dd61X7Dd0TDhIfu+tVIblUSIPrwHJWIkhN+x3vGi1b9JEoJb+avRWpGT/2a4ERWJcPm2sGDjBC15DpKZ2VJWblJyFbBS1oYJyfLr0w9wPBegXRoakSOUzQML49NnA7AYHcE7T7/mH3XpGjH0SXH2glZ6Vhsb+yNlX7XYZz+3megdkNC0yUtKNlhuPQok8IG+tdQu6xrsF/c4w0MyHgzZIUPi8J4Vv40cKP/xsGUDVGBgsgkPhxkiRpOPfD0EbMr7dB/L8ziEYGjjcLwjRdLHzzLZJ0/lIn2GiAUN9JVUKtHjPcJHMvWq84rTQEl2L7O/PXsjGhJrWGerZelYzspSDc5OV4YhgAbEE5vzH1/I/qAXZXIXGo81q/hyiFl8Fk9DiE8ByGPV4/MfhTtB57ApsC5ZfDoq464T847YeXsDodR6Rp+PHjod5RlduHaSh2F1CCcjq96SNMU2Yux4IE/WlYYIFXYD1hOIjZZ8AeG64358uu/ryGp7CeoAtJ/ovpPq9hQteGPQT6JJse5NIpK5B5/jJ8Y5sqVcIjPcQ22h0Yh6r5N5kEqGV3zk37jGjT5disPemBFC1pRQ5c354WM3we/05LdqghkIddn9i91BYiw+fxB1NC+oeCdvpDC484/NFBsQz/eb1ZpNvrPHASb5M44ZxGThpttk4UXm222zz1Au/qr0mv9S86LdsaQrXww2XLoR9Tw2UH8jdnGVjzPOnp2xcVaE+5p0HsvYt8z8nnnu+EMUmcJJ5HTh75wTYON9dRHk24R6/syDzX9/vezpCPlprVlDMx+mr00FQKToLpM5dwR0+45757/R0AAP//AwBQSwMEFAAGAAgAAAAhAIRjXwIoAgAANAUAABQAAAB4bC9zaGFyZWRTdHJpbmdzLnhtbHxU207bQBB9r9R/GPkd7BASoEqMEtpQlYuqhDZS38b2xN7GnjW7a9LmW/opvPFjnVwQrW149OzMucye9eD8V5HDAxmrNA+9zmHgAXGsE8Xp0Pt2Nzk49cA65ARzzTT0fpP1zsP37wbWOpBZtkMvc6784Ps2zqhAe6hLYjlZaFOgk0+T+rY0hInNiFyR+0dB0PcLVOxBrCt2Q++060HF6r6ii12he+KFA6vCgQtvNDtMB74LB/6msqt+VMSiq1G/Uc6tdJw1+jWzuGyZmBhSLeUZFm3dM8Fp6b5T8ZIc9ILj4zrznFTGGGeOuH40c1Rm2OrjlqqfmJn6xJiMIOk8adN2kVG8zJV1VB97lnfUOakfCREZEFwlwDAykWzDGixLbRwcQKQsdAL4glxhQ81Ux8sV5Xkd8+lPtEmUANoyx4btPWWVpOSkcUu8Y/rECcGEItNC9uyhc9ap822usF67xHVBnMDF7LqxjadHsbk3nJfqfu9zy97udE/e7Z/062jfxz9ggvkm/TAn8/ryg15D+BSrQh7fmmQHLJseVTbCqiUpz/ynZ923L/C2cmsyCQrGAcBlcNQL+vDVKA2fW17FRLbQjOUViwbxs5YXX2c7hrngNGeWpJjgrckrZKcZrivR14jEiEu0tuLUrp4eebvLqYp1nX3EOaYU5eg22RT1TqXbiI54gTL8Sk5f4t+rI6bp4vA/2bCQdMD433C+zPjy1wv/AgAA//8DAFBLAwQUAAYACAAAACEAO20yS8EAAABCAQAAIwAAAHhsL3dvcmtzaGVldHMvX3JlbHMvc2hlZXQxLnhtbC5yZWxzhI/BisIwFEX3A/5DeHuT1oUMQ1M3IrhV5wNi+toG25eQ9xT9e7McZcDl5XDP5Tab+zypG2YOkSzUugKF5GMXaLDwe9otv0GxOOrcFAktPJBh0y6+mgNOTkqJx5BYFQuxhVEk/RjDfsTZsY4JqZA+5tlJiXkwyfmLG9Csqmpt8l8HtC9Ote8s5H1Xgzo9Uln+7I59Hzxuo7/OSPLPhEk5kGA+okg5yEXt8oBiQet39p5rfQ4Epm3My/P2CQAA//8DAFBLAwQUAAYACAAAACEAFPrLrjsBAABIEQAAJwAAAHhsL3ByaW50ZXJTZXR0aW5ncy9wcmludGVyU2V0dGluZ3MxLmJpbvJn8GbwZFBg8GVwZrBgMGMwZNBgCACyfRg0GRjwyOkAVeQDVTCyMLDeYcgREJ7PwsDIwMkwi9uEIx7I4mf495+JgQmoggnIoxyQawZIHwKjuuU/EDggOY0TyjbhmMWdAmVDfADyBQPQTwwMBw4cYIDJUcFbOIxA9S2EB/MFEwPIfWxgnYzQsGUEuw/hU9q5bKSZDAl1WAwwMfjjzS+EQ2cLN0gNLN4QbNzpmwuYk6iRg7C7TQqajjBlIXaC0voMoHtB6R/mWg64YhYUbWwMoFQpAoTUAbTzNZUcOGoMXUKAGYctJ1hNwEkx4DADAwhDAXUqnNG4HQ0BqoYAotQcDVh6hwCiJkHUZAqj0TAaAqMhMBoCQyAEDov5jBZXdIkn0JgANotERUUZQJiLiwsFyzHIgZUDAAAA//8DAFBLAwQUAAYACAAAACEAgSQdjVwBAACiAgAAEQAIAWRvY1Byb3BzL2NvcmUueG1sIKIEASigAAEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAjJLBS8MwGMXvgv9Dyb1N0m7Dha4DlZ0cDDZRvIXkWxds05BEt/33pl1Xp/PgqZT33o/3PpLPD3UVfYJ1qtEzRBOCItCikUqXM/S8WcR3KHKea8mrRsMMHcGheXF7kwvDRGNhZRsD1itwUSBpx4SZoZ33hmHsxA5q7pLg0EHcNrbmPvzaEhsu3nkJOCVkgmvwXHLPcQuMzUBEPVKKAWk+bNUBpMBQQQ3aO0wTir+9Hmzt/gx0yoWzVv5owqa+7iVbipM4uA9ODcb9fp/ss65G6E/x6/Jp3U2NlW5vJQAVuRRMWOC+scXaw5brKHxUCTbHF1J7xoo7vwwX3yqQ98cr97XjHFpZpT3IIiXpKKZpTMmG3LHxiI2nbznuc2dTKNTtP7UCGYVF7LT/rLxkD4+bBRp4ZLqhE5ZljLS8X/l24QlY993/QewbjhmhF8QzoOhK/3xVxRcAAAD//wMAUEsDBBQABgAIAAAAIQAcJU1gwAEAAMIDAAAQAAgBZG9jUHJvcHMvYXBwLnhtbCCiBAEooAABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAKRTwW7bMAy9D9g/eL43crqiGAJZRZts6GHDAsTtnZbpRKgsGRJjJPue/cl+bLK1OO5a9LDdSD766fGR5jeHRicdOq+sydP5LEsTNNJWymzz9KH4cvEpTTyBqUBbg3l6RJ/eiPfv+NrZFh0p9EmgMD5Pd0TtgjEvd9iAnwXYBKS2rgEKqdsyW9dK4srKfYOG2GWWXTM8EJoKq4t2JEwj46KjfyWtrOz1+cfi2AbBgheWQBeqQZFxdk74bdtqJYHC9OKbks56W1Py+SBRczYFeVC9Qbl3io49xzTlGwkal+FBUYP2yNm5wO8RejPXoJwXvKNFh5KsS7z6Eey8SpMSPPYy87QDp8BQkNu3xWSIdevJiVtXoiJf6l8/idBxFroiMoTTD6axuhLzoSEEbzZGrjs0YAxhcocOVdjl/7/TC41TBwHP/SgUafTf6zU4esWey6k9g75oTpRaQIlaY5zujxOjJyfww8rt5VMZh3kxy7CLoOovHUvbtGCOARijr8o8+Ye2sCsgPO35eZFvduCwCqcx3sFY4PdhxU73JMsdmC1Wp56XQH+Vj/GXFPPrWfYxCwc3qXF2/vnEbwAAAP//AwBQSwECLQAUAAYACAAAACEAQTeCz24BAAAEBQAAEwAAAAAAAAAAAAAAAAAAAAAAW0NvbnRlbnRfVHlwZXNdLnhtbFBLAQItABQABgAIAAAAIQC1VTAj9AAAAEwCAAALAAAAAAAAAAAAAAAAAKcDAABfcmVscy8ucmVsc1BLAQItABQABgAIAAAAIQDJiEqruAMAAD4JAAAPAAAAAAAAAAAAAAAAAMwGAAB4bC93b3JrYm9vay54bWxQSwECLQAUAAYACAAAACEAgT6Ul/MAAAC6AgAAGgAAAAAAAAAAAAAAAACxCgAAeGwvX3JlbHMvd29ya2Jvb2sueG1sLnJlbHNQSwECLQAUAAYACAAAACEAtxqQ9pYGAAAsHwAAGAAAAAAAAAAAAAAAAADkDAAAeGwvd29ya3NoZWV0cy9zaGVldDEueG1sUEsBAi0AFAAGAAgAAAAhAE7Fqqa4BwAACyIAABMAAAAAAAAAAAAAAAAAsBMAAHhsL3RoZW1lL3RoZW1lMS54bWxQSwECLQAUAAYACAAAACEA1fnqFIIDAAC9CwAADQAAAAAAAAAAAAAAAACZGwAAeGwvc3R5bGVzLnhtbFBLAQItABQABgAIAAAAIQCEY18CKAIAADQFAAAUAAAAAAAAAAAAAAAAAEYfAAB4bC9zaGFyZWRTdHJpbmdzLnhtbFBLAQItABQABgAIAAAAIQA7bTJLwQAAAEIBAAAjAAAAAAAAAAAAAAAAAKAhAAB4bC93b3Jrc2hlZXRzL19yZWxzL3NoZWV0MS54bWwucmVsc1BLAQItABQABgAIAAAAIQAU+suuOwEAAEgRAAAnAAAAAAAAAAAAAAAAAKIiAAB4bC9wcmludGVyU2V0dGluZ3MvcHJpbnRlclNldHRpbmdzMS5iaW5QSwECLQAUAAYACAAAACEAgSQdjVwBAACiAgAAEQAAAAAAAAAAAAAAAAAiJAAAZG9jUHJvcHMvY29yZS54bWxQSwECLQAUAAYACAAAACEAHCVNYMABAADCAwAAEAAAAAAAAAAAAAAAAAC1JgAAZG9jUHJvcHMvYXBwLnhtbFBLBQYAAAAADAAMACYDAACrKQAAAAA="
,"name":"Arbeitsplan_Stefan.xlsx"
,"type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
,"size":11495
,"lastModified":1733820901134
}]' AS CLV_Value 

	UNION ALL SELECT '[{
"file":"data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,<BASE64>"
,"name":"Arbeitsplan_Stefan.xlsx","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
,"size":11495
,"lastModified":1733820901134
}]' AS CLV_Value 

)
SELECT 
     CTE.CLV_Value 
     
	--,CASE WHEN ISJSON(CLV_Value) = 1 
	--	THEN JSON_QUERY(CLV_Value, '$[0]') 
	-- END AS object_0  

	-- ,ISJSON(CLV_Value) AS is_valid 
	,CASE WHEN ISJSON(CLV_Value) = 1 
		THEN JSON_VALUE(CLV_Value, '$[0].file') 
	 END AS short_file  
	 
	,CASE WHEN ISJSON(CLV_Value) = 1 
		-- THEN JSON_VALUE(CLV_Value, 'lax $[0].file') aka 
		THEN JSON_VALUE(CLV_Value, '$[0].file') 
	 END AS short_file  
	 -- https://learn.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver16
	 -- Der Zeichenfolgenwert im angegebenen JSON-Pfad 
	 -- würde abgeschnitten.
	--,CASE WHEN ISJSON(CLV_Value) = 1 
	--	THEN JSON_VALUE(CLV_Value, 'strict $[0].file') 
	-- END AS short_file1  

	,tExtractedFile.long_file 
	 -- ,JSON_VALUE(CLV_Value, '$[0].file') AS xxx 

	-- ,JSON_VALUE(CLV_Value, '$[0].file') AS xxx 
	-- ,JSON_VALUE(CLV_Value, 'lax $[0].file') AS xxx 

FROM CTE 

OUTER APPLY 
(
    SELECT myfile AS long_file 
    FROM OPENJSON(CTE.CLV_Value) 
    WITH (myfile nvarchar(MAX) '$.file')
	WHERE CLV_Value IS NOT NULL 
	AND ISJSON(CLV_Value) = 1 
) AS tExtractedFile 

WHERE (1=1) 
-- AND CLV_Value LIKE '%file%'









SELECT 
	 CLV_CLS_UID 
	,CLV_ELE_UID 
	,CLV_Value 

	,CASE 
        WHEN ISJSON(CLV_Value) = 1 THEN 
			CASE
				WHEN JSON_VALUE(CLV_Value, '$[0]') IS NOT NULL THEN 'array' 
				WHEN JSON_QUERY(CLV_Value, '$') IS NOT NULL THEN 'object' 
			END
		ELSE 'not json' 
	END AS json_type 	
FROM T_Checklist_ZO_ElementValues 
WHERE NULLIF(CLV_Value , '') IS NOT NULL 
AND CLV_Value NOT IN ('true', 'false') 
AND TRY_CONVERT(datetime, CLV_Value, 104) IS NULL 
AND TRY_CAST(CLV_Value AS float) IS NULL 
AND ISJSON(CLV_Value) = 1 

-- AND CLV_Value NOT LIKE '%json%'