Postgres JSON Query on Unknown Keys

Question

I have a jsonb column where the structure always remains the same, but the keys within the json may change. For example,

{
   "key-12345": 
    {
       "values-12345": [
          {"type": 5200, 
           "source": "somesource", 
           "messageid": 707643203507, 
           "timestamp": "2018-07-26T21:25:42.612Z", 
           "destination": "somedestination", 
           "previouslyRouted": false
          }, 
          {"type": 5200, 
           "source": "anothersource", 
           "messageid": 707643203507, 
           "timestamp": "2018-07-26T21:26:01.542Z", 
           "destination": "anotherdestination", 
           "previouslyRouted": false
           }
         ]
       },

   "key-6789": 
    {
       "values-34512": [
          {"type": 5200, 
           "source": "yetantohersomesource", 
           "messageid": 707643203507, 
           "timestamp": "2018-07-26T21:25:42.612Z", 
           "destination": "yetanothersomedestination", 
           "previouslyRouted": false
          }, 
          {"type": 5200, 
           "source": "anothersource", 
           "messageid": 707643203507, 
           "timestamp": "2018-07-26T21:26:01.542Z", 
           "destination": "anotherdestination", 
           "previouslyRouted": false
           }
         ]
       }
}

I know that the structure of that document will be the same, but the keys could be anything. I can pull the keys themselves out with

select jsonb_object_keys(column) from table;

easily enough but I don't know how to pull the object assigned to that key and deal with it. How do I select from a jsonb object based on the value of a key, rather than the values.

select object from document where json_key = 'key-12345';

montxe · Accepted Answer · 2021-04-14 01:30:34Z

0

This is how it's done:

SELECT object->>'key-12345' FROM document;

and with a where clause:

SELECT * FROM document WHERE object->>'key-12345' IS NOT NULL;

answered Apr 14, 2021 at 1:30

montxe

2,1492 gold badges19 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Postgres JSON Query on Unknown Keys

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related