0

Lets say i have 3 columns in my Table. (Postgres Database)

Name, FirstName, Communication

My Communication column is a string with this text.

[{"typ":1,"kontext":1,"adresse":"031 *** ** **"},
 {"typ":2,"kontext":2,"adresse":"031 *** ** **"},
 {"typ":1,"kontext":2,"adresse":"+4179*******"}]

Now i want to make a query with Name,Firstname and Mobilenumber

How can i catch the number inside the string where the typ = 1 and kontext = 2 and make it a new column?

user2210516
  • 613
  • 3
  • 15
  • 32
  • any database in particular ? – Roberto Hernandez Sep 17 '21 at 09:15
  • Hello @user2210516, Can you clarify your question? First you speak of name, firstname and communication as columns of your table. But in the next part of the question you have other properties in you json. Please edit your question more specificly – Ronald Haan Sep 17 '21 at 09:15
  • This is not really suitable for SQL. You might want to consider a redesign such that the database fits at least NF1 (no different information packed into the same column). – Aconcagua Sep 17 '21 at 09:17
  • @RonaldHaan Sorry if i was unclear. The json is inside the Communication Column. And i only want mobilephone number from that string inside and make a new select with the mobile number instead of the Communication column. – user2210516 Sep 17 '21 at 09:19

1 Answers1

1

You can use a JSON path expression:

select name, firstname, 
       jsonb_path_query_first(communication, '$[*] ? (@."typ"==1 && @.kontext == 2).adresse') #> '{}' as mobile_number
from the_table

This assumes that communication is defined as a jsonb column (which it really should be). If it's not, you need to cast it communication::jsonb. It picks the first array elements where typ equals 1 and kontext equals 2.

jsonb_path_query_first returns a jsonb value and the expression #> '{}' turns that into a text value.