Postgres jsonb how to check some values is not empty

Question

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?

Do you really store keys with "empty" values, or would the keys simply not be part of the JSON? e.g. would your JSON look like this {"mts": "", "tele2": "", "beeline": "56", "megafon": "377"} or like this: {"tele2": "", "beeline": "56", "megafon": "377"} — user330315
– user330315, Commented Oct 10, 2018 at 8:08

S-Man · Accepted Answer · 2018-10-10 12:18:38Z

2

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.

answered Oct 10, 2018 at 12:08

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Answer 1