Get two values from the same table

Question

I have a table called Register

Id, Type_status, Status, Description

Example:

ID      Type_status   Status    Description
1       8000          8017      test

And i have a table called History with the following structure

Id, Id_Register, History_status, Payload

Example

Id      Id_Register   History_status    Payload
1       1             8015              {"name": "test"}
2       1             8016              {"token": "example"} 
3       1             8017              {"email": "test@test.com"}

What I need is to be able to execute a query that will bring me the value of Record_Id and Payload value from the History table when the Id_Register = 1 and the History_status is 8015 and 8017 with History_status 8017 is email = test@test.com

The expected result is

Id_Register      Name    Token
1                test.   example

I need to get all the names and token of the records that in the 8017 state have as email test@test.com

Mention that I have already solved the comparison of the text in the json, what I could not achieve is the query to obtain the two values for the same table depending on the states that I mention.

JSON_VALUE(cast(Payload as nvarchar(max)), '$.email') LIKE '%test@test.com%'

Perhaps you need to normalise the payload column into something like payload type and payload value — Peter Smith, Jun 12 '20 at 12:57
@PeterSmith How to compare the json value I have already solved it, but what I can't get is to get the values of both registers for 1 only Register_Id... — sioesi, Jun 12 '20 at 13:01

Marc Guillot · Accepted Answer · 2020-06-12T13:20:00.253

You just need to join twice the table History with the table Register. The first join will get you the names, and the second the tokens. To differentiate each one you use aliases.

select R.Id, 
       json_value(N.Payload, '$.Name') as Name, 
       json_value(T.Payload, '$.Token') as Token
from Register as R
     left join History as N on N.Id_Register = R.Id and 
                               substring(N.Payload, 1, 8) = '{"Name":'
     left join History as T on T.Id_Register = R.Id and 
                               substring(T.Payload, 1, 9) = '{"Token":'

score 0 · Answer 2 · answered Jun 12 '20 at 13:07

0

Maybe this entry in the SQL server manual can help you, this entry shows you how to obtain a scalar value from a JSON column, so you can get only the email address from the json and use it in your where clause to filter. The best title for this question would be how to get a specific entry from a json column, something like that.

https://learn.microsoft.com/pt-br/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

answered Jun 12 '20 at 13:07

Leandro Jacques

413
5
19

Leandro, thanks for your response. The truth is that the problem is not obtaining the JSON value, but the query itself, being able to access both records of the same table but with the record ID in common. – sioesi Jun 12 '20 at 13:08

Get two values ​from the same table

2 Answers2

Get two values from the same table