CODEWITHSUNDEEP
sqlsql-servert-sql
cj devin
cj devin

Reputation: 1375

JSON_VALUE selecting different row result

I have table that contains json string in column. While i am doing query to select json value using JSON_VALUE result not coming as expected for the query.

Person Table detail:

 CREATE TABLE [dbo].[person](
    [UUID] [nvarchar](255) NOT NULL,
    [Payload] [nvarchar](max) NULL,
    [CreatedBy] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED 
(
    [UUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

query:

select uuid,createdby as fullname ,
  json_value([Payload], '$.name') as Name
 from person

Here issue i m facing like in Name column value selecting from different row and that value not existing in same uuid and in row Payload.

Sample rows Data using for json path:

[{"UUID":"123","Payload":"{ \"author\": \"abc\", \"name\": \"data:image\/png;base64,iVBORw0KGgoAAAANSUhEUgAAApMAAACbCAYAAAA+2hC5AAAgAElEQVR4Xu2df+w821nXi1RBiq3F0h+IuNUmSJUUEBQTg1ujRiOhFwMIJIStaSFIgrdEI5hIP40m\/PqDSyyICHbBYIEmcFE0oNUuGAkaEETEKCWslWoLLVVsKbTF6\/O6zGPPd76zOz92ZnY+s6+TPJnZ2TPnx+ucnXnvc86Z+YCnGCQgAQlIQAISkIAEJDCQwAcMPM\/TJCABCUhAAhKQgAQk8BTFpJ1AAhI4ReB3xhcvCvvbYW8Me6moJCABCUhAAnUCikn7hAQksA0ET4RtKvu42GJ8zvCW2HmeqCQgAQlIQAKKSfuABG6PAMLwT4ThaUQ4EnJ7jsZ\/iy9\/MuwlVaTPiu3rbg+fNZaABCQggXME9EzaPySwPgIIRcQj2zbR+B8izv+qEByqfQQkxnHOf0P1\/cti+63rw2WNJCABCUjgEgKKyUvoea4ElkFgU4nHRyrxhwcyw\/+uhGEKxGMhFNtKXwrJX4jIv6ftBL+XgAQkIIHbI6CYvL02t8bLJ4CIe0YYw9MExCICsRSJiMIXhL0vjPhl+L74gHh8vNoOqfEL46T\/VJz4p2P\/9UMS8hwJSEACElg3AcXkutvX2i2fAAIxh6QRj3Vh2KUGzG1EOB6qbZdz2uJ8d0T4zCrSV8f2y9pO8HsJSEACErhNAorJ22x3a30dApvI9vdWgpH9XDVdL80PxYFjzZpK\/Jw4+F\/C8EKOGUoheYiEWXjzS2NmYFoSkIAEJLAeAorJ9bSlNVkOAYTiF4Y9N4x9vI85ZF0vJV5FBBuCMLfXqsk2Mn5lGNsMPFtyf60Cma8EJCABCSyfgGJy+W1kCe8HAUQjj9DZnRGOrJw+VsIxxWOupL5mLb+gEpCfUyvE18XnL71mwcxbAhKQgASWT0Axufw2soTLJYDHMQXktigm3sZ\/E\/b3wxCLCMgliMaSJCu\/mavJdtOA+C6OvWq56C2ZBCQgAQkshYBiciktYTnuC4EUkIgwLAOP4GERzD7ssNDKUF7EL9tyZXhZXMqOiFxqHRaK1mJJQAISuF0CisnbbXtr3p1Am4BERGJLCH8+CvEJYb9SCUbmalL+bUvh3hHfvzrsK5ZQCcsgAQlIQAL3h4Bi8v60lSWdl8ApAUkpeI7j\/soCchP5szI8HyfElmN9AvU4VPU49jnRuBKQgAQkIIEkoJi0L0jg\/QTaBGR6IOee\/0i5XhS2rcTjEOGYtURAXqse9jUJSEACElghAcXkChvVKvUisDQBuYnS57MoEY1DhOMTcd67w342DOF7CFvS6vFeDWRkCUhAAhJYNgHF5LLbx9JNQwDBxkKUbVi5iIbc5vTcUQ5WVLOlLDm\/sW+tech5isVjtd83DeNLQAISkIAEBhFQTA7C5kn3kEAKyF0l2soqzCEgEYp1j+OpFdWn8LJiPEUjWwzxaJCABCQgAQlcjYBi8mrozXgGAjmE\/ejMAhLhyBzHHKYe4nEshSOC8aBwnKHHmIUEJCABCfQmoJjsjcwTFk5g7jmQDFOXopH9voGHnKdg1OPYl57xJSABCUjgqgQUk1fFb+YjEsiHce9qafIKw8fCWMF86SrssYRjCsZDlIn9S8s1IkaTkoAEJCABCfQjoJjsx8vYyyKAF\/DzwxCQ5fxDBOS+EpDHAUUmrXwUzyb20\/PYN6l8F3eKRoVjX4LXiU+bM7+1KXDNtB2v0y7mKgEJLJSAYnKhDWOxThI4NQ+SoWK8j4hIbvZdQykcEY0pHruen\/FyRfWxyh8BabgOAdqUdnxGtWWfkMfzjwfbIdMS6rXCs1z2uWz78jh\/LPRAX6c\/mKsEJDAxAcXkxIBNfjQC3PT\/StgjlSjIhPu8jaYuHFM89i1kCsccru4jXvvmteT4iLS6Bw+mKdaeG\/sfHPbJ1TbrwrGnFsc+tEcl3xdx3xP2a2G8MvKZYTxXk\/T6pNMjy1GjlgIzPZzlsZw\/O2qmJiYBCUhgSgKKySnpmvalBM55IR+LxPdhp7w9CJ1cUb2NfT5jfQI39lIwsn\/sk8DC4yL88N4RYJ2euyx2KQw5NoThwhE8WTwEKoZAzc\/vLAr+7Nj\/kCtUJMVm\/lmhr9P\/8HLe6h+YKzSDWUpAAm0EFJNthPz+GgQQLXghd2Hp5aIc3xa2DzvUCkX8UjjWRVCXOuBt5EZdisclD0vChXrilSOkECx5TSkGEV94A0+FX694ItBK4fPG+PyWMMQa26aQ3KlLuV+PiyeSts9yZJpsm3gks0yHz\/SboYG6kBflYEtZKc+7qu3Thibc4bxfjjjfHsZvQmHZAZhRJCCB6QgoJqdja8r9CWzjlFeGsc2Ad3Afhicyb9blMxyJWwqotlyX+BgeBAjDxSn+\/lTsv61WL74j9KnrKRY8wzK9XMQp958Tnz8oDI\/lh4W9IOzU8HE+CzM9aIeIe6ysrR2W+D3tgGXYFsw5nuzZP7VA51S9EM6\/EfbeMIT408MYpofvpYH2ezwM\/kz7WPKfoEvr6vkSkMACCSgmF9goN1YkbtCsyH40jJt0BjyF\/yjsrWEIqW217SqmUjQidI5hbFP0zI04PWDUr8n6lAcBR31KwcBnLEO9nhm\/PAemfzHsz4QhauB7KuTikUOVT\/JUtDw49J9tC8fcTw9pTifo0tZ4PPG25rA7c0w\/MAxx38XbibDE8FoaJCABCUxOQDE5OWIzOEGAmy1eyEfCUiAyPPiz1U30DxTH2yDmghjEzSFsbtFIXfBUUQ9EGp9LO1f+FL3HiJT27thHRFOf0mvYxuHc95SH52Rua8zLc3Kon3KUIvySfD33QQL0D\/rJp4T9lqLP5B+OMXnRjq8IQ1gaJCABCUxGQDE5GVoTPkEAMfOVYazwzfB\/qxtrG7R8biNC5xDGzRKbOuSNPsVifduWPyINUZgit9y2nTv0e8qIeESsw3xTS4gyITL+a9g\/G5qJ541OID2ZbGk3Am2HDRWcDLF\/U9gxzMU7ozeZCUpAAopJ+8DUBLgJMsdxF8ZcwC6PbylF4xwesrpYhAk38hSN5xiVw86HiJiikXP4PGegzLwJiC0esDLgAaU8CMgs55xlM69xCaSHcxPJlvbC+PzhHbKiD6Txx8IgAQlIYDABxeRgdJ7YQICbGsO92zBudi8OOzdXrJyLh2hM4TgVXMpEeShf\/SZ8Ls+82XLzJdS3U5W3S7p4HvNVkojfDCkeKSt27JKYcVZD4C9HTb4mrMscy+zT2VcUl6vpBlZEAvMQUEzOw3mNuWyiUngcGa5mEcfvCyvFTL3OLCb4t2EsqmFolRvXVIGypahlHxFZ99SVeZeLdfAsHivL4eipyjk0XeqUr5FkP0MOXcOWshtumwB9nr5Q\/qH7gfj8g9XvYVv9TuqU+A1wHsbqcH4PBglIQAInCSgm7RxtBHIOV\/1xPE3n8ew7Hu7M6tMM\/z12viJs35bRwO9ziJobI5bDf03JlQtMuFEScjsw+1lP20Ru9UVLeHcfC2P4GhFgkEBJgN8DfaN8lNGr4vNdFYk+lb8dtk2PPOKPCb8TVof7J8X+JQEJPERAMWmn4ObBkBjhH4c9Kyw9eWy52dRDzhPkBsOK1E8M+2NFJL7\/0bBXh33\/yIgpT65KPuVxxNOYw+aU8VjZyEWZLTkEMw9xfzQsvb\/c2BGR3txna4Z7m1GTh\/LTozaIzHrg98XUiW0Y0yfqgT8snIfhtTRIQAISeIpi8nY7ATcYvI14up5\/BkM5BJweimN1w+Fc0snQ5z3ZXclzc0vxyA2Oz2VAuB7CsmzsrykgIOFcisi7+EwbGCTQlUBdUPJnr\/wDeCodhGWKyyav5Zvi+39Q9Uf6pPMtu7aI8SSwIgKKyRU15omqbOJ4uSiGz6UALE\/jzRzfUd0YDrFFoNWHTjn3NUUaiM19GF6yetyhdHNFMjcxylsGhnWzbGyPQzNZ+HnU\/euK+nOT3q24vgtvjlUU72OiFj9T1aSrmCwrzm8\/xeW511ByHcg\/d2zz6QyrgGglJCCBhwkoJtfTK\/Bc5bxGBBgXfiw9WmVN8zV4vDavfN7jXXxmPlVTIB08ZHjKCKTB\/n4EhJS3FJBlkrf2SBtYINa3FQTqD2eGFQ0SuJQAv+G7KhFGJI4DE6SffkYY4pLHfbW94xyBeaiMP0aITIMEJLASAorJ+9eQXMTzPc7snxON1C4fUsxNg4s5W2wb9oai+t8a+18axqv16oG4CBzyIyA4L\/VE5gO1697HHLZGPGV5G4q0ukOIdTyRu6pmcIDx3epqaoWuTeB\/RAGeF\/bi6jc2Rnnov1yLuFbkNenc+8sRl\/zGMedejtECpiGBKxJQTF4RfkvWm\/i+fLwNn095GkmqXHTCv\/5j2Ll\/\/whJLvwZ8FDy6J4ykCcCB8FHuGSolZtNKSBLjyllzxvLoYXLGr\/G88gCG3gTWFzDMW64BgmMSSA9kz8fif7RsF8aM\/FaWvTnFJjb2D\/nvdxX1wCF5YQNYtISmIqAYnIqst3T5WJbf5A2F95TIRfEHCJCF9HYlE451MX3rwv7rFrEr43PLwtD9A0d0ubcfKB2CtLMBmGaAvLYHdeqYtL2iPVsb9p2F0bbGiQwBQFenfnnwu7CTk1pmSJf0uR6wHWA\/s626YUGXAv2YV8f5p+pqVrCdCUwMgHF5MhAzySHcCiHpzfVRfXUKYgtLqzYUNHYlPY3x8GX1774g\/E5J+ZTznKBTV8vGfVqmv+IIEU8Hqrtrd8odhVnmgI2d2GPzdcdzekGCfDbxCNJaBqJmBsJghLjAfxNYR8HEbzHuQtmfhKQQD8Cisl+vM7F5l93ehm5aD83jDfD8BxGjp8KpWg8VBfOqS6e20i\/nCdJmRB4PHOO8jPUelcV9H\/GFs8knoy2QH0RkLtaXVNApgeyLZ1b+Z7+8BNVZZnTyg11qja\/FabWs51A+ftf0rWfaw\/XDqZ2NM2z3MdxPJX8qTZIQAILJLCkC8oC8TxUJC7GT1SCiQsgnwm5PVWHXD1dehjZn9M7R3kRknVhyz9\/ylI+hoZ5S7uW8m3i+yYBeevzH7v042NE4qZJv4DjnP2gS\/mMs04CXKe4BvAH5twf3GvWnj9WiErmV9fDIQ5wvWJrkIAEFkTglsUkwzy89q+8aCGEPj7sT4a9rWqn9Di2NRsXaERBisRj7P9iWBfPXlvaY3x\/F4kwV7IMvx4fXhu2qw62DbduIt45Abmv6j9GedeaBkPZeIAJrwhzaHutLb28eiHS+NPIn0VE25LDNgp3F3ZKVHKtYQqOQQISWACBWxSTvyO4Nz3+pq05cuEL8Q5hx5q1nX\/N7zeRec6VKsuB+EUsExDDu7D6UBLnKiDHaT1Y\/lwYUx\/eHPaR4yRrKhLoRCD\/yODdu+t0xvUj8ZuhrE3zKo\/Vd4rK67eTJbhxArcoJnkP9TfU2p3nrr0njHmCbwlDbCKqeFXY96ygjxyiDk3\/8LNqXIx3RT25gH922BeFfVRxPIew9xWfFaCZtQpfELn9vSrHb4ztF8+au5ndOgGuaTye59R7uZfMh2vSXRge1foq8GMc45rEdYx9gwQkMDOBWxSTHx6M\/2XYx\/ZgzYXqqWE\/HfaDYfdpIvg2yltfdJNVLx\/5w8VaD2SPTjEgavlsz3IF\/YCkPEUCvQjw+2Z0gt98j\" }","CreatedBy":"abc"},{"UUID":"456","Payload":"{ \"author\": \"xyz\" }","CreatedBy":"xyz"}]

Current Result: currentresult

Expected Result:

image

How to solve this issue?

Thanks

Upvotes: 0

Views: 677

Answers (2)

lptr
lptr

Reputation: 6798

...check that the payload string has a json key = "name" and no invisible/non-printable characters have crept in there:

declare @t table(id int identity, payload nvarchar(max));
insert into @t(payload)
values
(N'{"author": "abc", "name": "data:image..png;base64"}'),
(N'{"author": "abc", "na'+ nchar(8299) +N'me": "data:image..png;base64"}');

select *, isjson(payload) as payloadisjson
from @t;

select *, 'payload like %name%'
from @t
where payload like N'%name%';    

select *, json_value(payload, '$.name') as jsonvalue_name, '??'
from @t;

select *, json_value(payload, N'$.name') as jsonvalue_name,  'json key is not N''name''', 
cast(op.[key] as varbinary(20)) as keybin, cast(N'name' as varbinary(20)) keyshouldbebin
from @t as t
cross apply openjson(payload) as op
where op.[key] like '%n%e%'
and cast(op.[key] as varbinary(20)) <> cast(N'name' as varbinary(20));

Upvotes: 0

Emin Mesic
Emin Mesic

Reputation: 1811

Per your last update you have provided the following JSON:

[
    {
        "UUID":"123",
        "Payload":"{ \"author\": \"abc\", \"name\": "test" }",
        "CreatedBy":"abc"
    },
    {
        "UUID":"456",
        "Payload":"{ \"author\": \"xyz\" }","CreatedBy":"xyz"
    }
]

I have removed base64 because of JSON readability.

Where is the problem, in this JSON there is another object called Payload and unfortunately values in this object are stored as a string, not as JSON object. Altough it looks like JSON is not treated as JSON object. This is a problem for SQL Engine and JSON_VALUE function.

SELECT *, 
       JSON_VALUE(value, '$.UUID'), 
       JSON_VALUE(value, '$.Payload.author'), 
       JSON_VALUE(value, '$.Payload.name'), 
       JSON_VALUE(value, '$.CreatedBy')
FROM (select * 
      from openjson('[{"UUID":"123","Payload":"{ \"author\": \"abc\", \"name\": \"test\" }","CreatedBy":"abc"},{"UUID":"456","Payload":"{ \"author\": \"xyz\" }","CreatedBy":"xyz"}]')) ojson

The first query will return you all NULL values when you try to access Payload, but the second query will produce the correct data because \ and " are removed from the object and they are not necessary anymore. Compare these two JSONs used in examples and you will see.

SELECT *, 
       JSON_VALUE(value, '$.UUID'), 
       JSON_VALUE(value, '$.Payload.author'), 
       JSON_VALUE(value, '$.Payload.name'), 
       JSON_VALUE(value, '$.CreatedBy')
FROM (select * 
      from openjson('[{"UUID":"123","Payload":{ "author": "abc", "name": "test" },"CreatedBy":"abc"},{"UUID":"456","Payload":{ "author": "xyz" },"CreatedBy":"xyz"}]')) ojson

Upvotes: 1

Related Questions