How to check for existence of nested JSONB property key?

[Alban Hertroys <alban.hertroys@xxxxxxxxxxxxxxx>]

    Hello all,
      
      We have a table with material properties stored as JSONB objects,
      as the available properties differ wildly between different kinds
      of materials. It's semi-structured data.
      I am in a situation where I want to filter our materials based on
      the existence of a certain property, regardless its value.
      
      A query for such a property with a specific value is easy, that
      even uses a GIST index we defined on that table.
      For example:
      select obj -> 'top' -> 'next' -> 'key' as key_value
        from (values('{"top":{"next":{"key":3}}}'::jsonb)) x(obj)
       where obj @> '{"top":{"next":{"key":3}}}';
      
       key_value
      -----------
       3
      (1 row)
      
    I'd like to do the
      same for an "incomplete" search object, where the "value" is an
      object instead of a literal:
    
    select obj -> 'top' -> 'next' ->
        'key' as key_value
           from (values('{"top":{"next":{"key":3}}}'::jsonb)) x(obj)
         where obj @> '{"top":{"next":"key"}}';
        
         key_value
        -----------
        (0 rows)
        
        I was hoping for the same result as above. Apparently the jsonb
        contains operator requires an exact match?
        
        Is there an approach that can do this, preferably making use of
        the GIST index? Mind that the nesting depth of the search object
        can vary, like the material properties vary, I'm looking for a
        generic approach here.
        
        We're on PG 11(.9), so the enhancements made to JSONB operations
        in PG12 are not (yet) available to us. This is a 3TB database
        w/o replica's, so upgrading it is a little scary and requires a
        maintenance window that would be sufficient to restore a backup
        in case things go wrong.
        
        Regards,
        Alban Hertroys.

Alban Hertroys    
D: 8776 |M:  |T: +31 (0)53 4888 888 | E: alban.hertroys@xxxxxxxxxxxxxxx
Apollo Vredestein B.V.| Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands
Chamber of Commerce number: 34223268

 

The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Vredestein and its subsidiaries rule out any and every liability resulting from this or any other electronic transmission.

Please consider the environment before printing this e-mail

CIN: L25111KL1972PLC002449

Registered Office: Apollo Tyres Ltd, 3rd Floor, Areekal Mansion, Panampilly Nagar, Kochi  682036, India



Disclaimer:

The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Tyres and its subsidiaries rule out any and every liability resulting from this or any other electronic transmiss