Postgres WHERE array contains empty string

Question

I'm trying to do select * from demo where demojson->'sub'->'item' = array("") but this doesn't work. I'd like to find the following

All rows where .sub.item in the JSON column is an array containing exactly one empty string ([""])
All rows where .sub.item in the JSON column is an array that may contain more than one item, but at least one of the items is an empty string. (["not empty", "also not empty", ""])

demojson column could contain for example

{ 
  "key": "value",
  "sub": {
    "item": [""]
  }
}

I can't validate this text as a valid json file. jsonlint.com — McNets
– McNets, Commented Jan 17, 2017 at 20:33

Mr Tarsa · Accepted Answer · 2017-01-18 11:09:08Z

1

Have you tried

SELECT * from demo 
WHERE demojson->'sub'->>'item' = '[""]';

Here ->> operator allows to get JSON object field as text.

And another solution

SELECT * from demo 
WHERE json_array_length(demojson->'sub'->'item') = 1 AND 
      demojson->'sub'->'item'->>0 = '';

Here ->> operators allows to get JSON first array element as text.

edited Jan 18, 2017 at 11:09

answered Jan 17, 2017 at 18:34

Mr Tarsa

6,6613 gold badges29 silver badges45 bronze badges

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

2 Comments

IMSoP Over a year ago

Since you're not using any wildcards in your pattern, you probably want just = '[""]' and = '' rather than LIKE '[""]' and LIKE ''

Mr Tarsa Over a year ago

@IMSoP Thanks for suggestion, it makes sense.

McNets · Accepted Answer · 2017-01-17 21:00:00Z

Due JSONLint doesn't validate the supplied text example, I've used the next:

CREATE TABLE info (id int, j JSON);
insert into info values 
  (1, '{"key":"k1", "sub": {"item":["i1","i2"]}}'),
  (2, '{"key":"k2", "sub": {"item":[""]}}'),
  (3, '{"key":"k3", "sub": {"item":["i2","i3"]}}');

Using the where clause in this way, it works:

select * from info
where j->'sub'->>'item' = '[""]';


+----+------------------------------------+
| id |                  j                 |
+----+------------------------------------+
|  2 | {"key":"k2", "sub": {"item":[""]}} |
+----+------------------------------------+

Can check it here: http://rextester.com/VEPY57423

Laurenz Albe · Accepted Answer · 2017-01-18 10:25:15Z

1

Try the following:

SELECT * FROM demo
WHERE demojson->'sub'->'item' = to_jsonb(ARRAY['']);

answered Jan 18, 2017 at 10:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Postgres WHERE array contains empty string

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related