CODEWITHSUNDEEP
postgresqlpostgresql-json
Siva Arunachalam
Siva Arunachalam

Reputation: 7770

PostgreSQL JSONB - How to match the empty string keys

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:

  1. Find the keys with empty strings: Ex: "": "VALUE1"
  2. Find the keys with just single quotes: Ex: "''": "VALUE1"
  3. 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

Upvotes: 0

Views: 2435

Answers (2)

Joe Love
Joe Love

Reputation: 5982

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.

Upvotes: 0

Joe Love
Joe Love

Reputation: 5982

 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.

Upvotes: 1

Related Questions