SQL JSON show one value of a string [duplicate]

Question

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?

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
– Ronald Haan, Commented Sep 17, 2021 at 9: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
– Aconcagua, Commented Sep 17, 2021 at 9: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
– user2210516, Commented Sep 17, 2021 at 9:19

user330315 · Accepted Answer · 2021-09-17 09:51:07Z

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.

answered Sep 17, 2021 at 9:51

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL JSON show one value of a string [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related