PostgreSQL JSONB - How to match the empty string keys

Question

Table Schema:

create table test_table
(
    id serial not null,
    data jsonb
);

Sample Data:

INSERT INTO public.test_table (id, data) VALUES (1, '[{"": "VALUE1", "KEY2": "VALUE2"}, {"KEY1": "VALUE3", "KEY3": "VALUE4"}]');
INSERT INTO public.test_table (id, data) VALUES (2, '[{"''KEY1 ''": "VALUE1", "KEY2": "VALUE2"}, {"KEY3": "VALUE3", "KEY4": "VALUE4"}]');

SQL Query:

SELECT id, arr_elem
    FROM test_table AS tt, jsonb_array_elements(
        (
          SELECT data
          FROM test_table
          WHERE id = tt.id
        )
    ) AS arr_elem
    WHERE arr_elem#>'{KEY1}' IS NOT NULL

I would like to tune to above query to match the following scenarios:

Find the keys with empty strings: Ex: "": "VALUE1"
Find the keys with just single quotes: Ex: "''": "VALUE1"
Find the keys with trailing spaces enclosed in single quotes: Ex: "'KEY1 '": "VALUE1"

Tried to escape the quotes and spaces and the query didn't return the expected results.

UPDATE 1:

Solution for 1: http://sqlfiddle.com/#!17/6d431/20

SELECT id, arr_elem
    FROM test_table AS tt, jsonb_array_elements(
        (
          SELECT data
          FROM test_table
          WHERE id = tt.id
        )
    ) AS arr_elem
    WHERE arr_elem->'' IS NOT NULL

Joe Love · Accepted Answer · 2017-09-29 20:02:44Z

1

 select * from test_table
 where data->'' is not null /*gets rows with blank key names;


 select * from test_table
 where data->'''''' is not null; /*gets rows with '' as a key name;

The last is a bit more complex...

 select * from test_table
 where exists 
 (select * from json_each(data) where key != rtrim(key))

This past one gets the row where any key != rtrim(key), so that should get items with trailing spaces in key names.

answered Sep 29, 2017 at 20:02

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

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

3 Comments

Siva Arunachalam Over a year ago

Unfortunately, none of the given commands works. sqlfiddle.com/#!17/6d431/16

Joe Love Over a year ago

I failed to notice that it's an array.. I'll have to add "unnest" to it.. the basics work, but they were not written for an array, but for a base object.

Joe Love Over a year ago

@SivasubramaniamArunachalam please see the other answer I just posted which should work given your exact data.

Joe Love · Accepted Answer · 2018-09-05 19:14:27Z

Another answer that's more appropriate for the json "array of objects" you have:

 select * from test_table
  where exists 
  (select * from jsonb_array_elements(data)
    where exists 
      (select * from 
        (select  jsonb_object_keys as k from 
          jsonb_object_keys(jsonb_array_elements)
        ) x 
        where x.k ='' or x.k ='''''' or x.k like '% '''
      )
  );

Note: the inner most (select json_object_keys as k...) shouldn't actually be necessary syntactically, but I never could get it to work as a smaller "one liner".

Also, this query selects all 3 conditions at once. You can easily adjust it to weed out these scenarios or do them one at a time by adjusting the 3 "or" sections of the where clause...

Explanation: you've got to first expand the data column to an array of objects using jsonb_array_elements to get each object, then turn, each of those objects has to be expanded to an array of keys, which can then be searched as normal text.

Collectives™ on Stack Overflow

PostgreSQL JSONB - How to match the empty string keys

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related