CODEWITHSUNDEEP
sqlpostgresqljsonb
itcoder
itcoder

Reputation: 327

Postgres jsonb how to check some values is not empty

I have a jsonb value

{"mts": "375", "tele2": "", "beeline": "56", "megafon": "377"}

how a can check for example that mts and megafon is not empty

my query is

select * 
from test 
where settings->>'megafon' <> '' 
   or settings->>'mts' <> '' 
   or settings->>'beeline' <> ''

But it is so longer query, how to write short condition?

Upvotes: 0

Views: 4457

Answers (1)

S-Man
S-Man

Reputation: 23676

SELECT * FROM
    jsonb_each_text('{"mts": "", "tele2": "", "beeline": "56", "megafon": "377"}'::jsonb)
WHERE value != ''

json_each_text() expands every json element as text element into one row each. The result is a table with columns key and value.

Now you are able to filter the value column for every content.

https://www.postgresql.org/docs/current/static/functions-json.html

Upvotes: 2

Related Questions