JSONB select result in one row-CodePudding

I need your help on this. I'm trying to achieve a query for a jsonb column information I have in a table. My jsonb is an array of objects and in every object I have two key/value pairs. In this case, I have a key/value to exclude and only get the another one key without it value. So, I figure it out how to do it like:

jsonb : '[{"track":"value","location":"value"},{"extra":"value","location":"value"},...{"another":"value","location":"value"}]'

SELECT id, jsonb_object_keys((item::jsonb - 'location')::jsonb)
FROM mytable, jsonb_array_elements(theJsonB) with ordinality arr(item,position)
WHERE offer = '0001'

This query, get me the result like

id   | jsonb_object_keys
-----------------------
1    | track
1    | extra
...  |
1    | another

But I need to get the result in only one row for each id like

id   | column1  | column2 | ... | column 
------------------------
1    | track    | extra   | ... | another 
2    | track    | extra   | ... | another
3    | track    | extra   | ... | another
4    | track    | extra   | ... | another

How I could solve this? Thanks in advance, I'm a pretty newbie in SQL but I'm working hard ;-)

CodePudding user response：

If you know the list of the resulting columns only at the runtime then you need some piece of dynamic sql code.

Here is a full dynamic solution which relies on the creation of a user-defined composite type and on the standard functions jsonb_populate_record and jsonb_object_agg :

First you dynamically create the list of keys as a new composite type :

CREATE OR REPLACE PROCEDURE key_list (NewJsonB jsonb) LANGUAGE plpgsql AS
$$
DECLARE key_list text ;
BEGIN
  IF NewJsonB IS NULL
  THEN
      SELECT string_agg(DISTINCT k.object->>'key' || ' text', ',')
        INTO key_list 
        FROM mytable 
       CROSS JOIN LATERAL jsonb_path_query(theJsonB, '$[*].keyvalue()[*] ? (@.key != "location")') AS k(object) ;    
  ELSE SELECT string_agg(DISTINCT k.key :: text || ' text', ',')
         FROM (SELECT jsonb_object_keys(to_jsonb(a.*)) AS key FROM (SELECT(null :: key_list).*) AS a
               UNION ALL
               SELECT jsonb_path_query(NewJsonB, '$[*].keyvalue()[*] ? (@.key != "location")')->>'key'
             ) AS k
         INTO key_list ;
  END IF ;
  EXECUTE 'DROP TYPE IF EXISTS key_list ' ;
  EXECUTE 'CREATE TYPE key_list AS (' || COALESCE(key_list, '') || ')' ;
END ;
$$ ;
CALL key_list(NULL) ;

Then you call the procedure key_list() by trigger when the list of keys is supposed to be modified :

CREATE OR REPLACE FUNCTION mytable_insert_update()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  IF NOT EXISTS (SELECT jsonb_object_keys(to_jsonb(a.*)) FROM (SELECT(null :: key_list).*) AS a)
  THEN CALL key_list(NULL) ;
  ELSIF EXISTS ( SELECT jsonb_path_query(NEW.theJsonB, '$[*].keyvalue()[*] ? (@.key != "location")')->>'key'
                 EXCEPT ALL
                 SELECT jsonb_object_keys(to_jsonb(a.*)) FROM (SELECT(null :: key_list).*) AS a
               )
  THEN CALL key_list(NEW.theJsonB) ;
  END IF ;
  RETURN NEW ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER mytable_insert_update AFTER INSERT OR UPDATE OF theJsonB ON mytable 
FOR EACH ROW EXECUTE FUNCTION mytable_insert_update() ;

CREATE OR REPLACE FUNCTION mytable_delete()
RETURNS trigger LANGUAGE plpgsql VOLATILE AS
$$
BEGIN
  CALL key_list (NULL) ;
  RETURN OLD ;
END ;
$$ ;

CREATE OR REPLACE TRIGGER mytable_delete AFTER DELETE ON mytable 
FOR EACH ROW EXECUTE FUNCTION mytable_delete() ;

Finally, you should get the expected result with the following query :

SELECT (jsonb_populate_record(NULL :: key_list, jsonb_object_agg(lower(c.object->>'key'), c.object->'key'))).*
  FROM mytable AS t
 CROSS JOIN LATERAL jsonb_path_query(t.theJsonB, '$[*].keyvalue()[*] ? (@.key != "location")') AS c(object)
 GROUP BY t

full test result in dbfiddle.