CODEWITHSUNDEEP
sqljsonpostgresql
Lysis90
Lysis90

Reputation: 77

how to extract a value from a json format

I need to extract the email from an intricate 'dict' (I am new to sql)

I have seen several previous posts on the same topic (e.g. this one) however, none seem to work on my data

select au.details
from table_au au

result:

{
   "id":3526,
   "contacts":[
      {
         "contactType":"EMAIL",
         "value":"[email protected]",
         "private":false
      },
      {
         "contactType":"PHONE",
         "phoneType":"PHONE",
         "value":"025/6251111",
         "private":false
      }
   ]
}

I need:

[email protected]

Upvotes: 0

Views: 57

Answers (1)

Not a bug
Not a bug

Reputation: 4314

select d.value -> 0 -> 'value' as Email 
from json_each('{"id":3526,"contacts":[{"contactType":"EMAIL","value":"[email protected]","private":false},{"contactType":"PHONE","phoneType":"PHONE","value":"025/6251111","private":false}]}') d 
where d.key::text = 'contacts'

Output:

|   | email         |
 -------------------
|1  |"[email protected]"|

You can run it here: https://rextester.com/VHWRQ89385

Upvotes: 1

Related Questions