Postgres jsonb: How to get mongo like projections?

Question

I have a jsonb column containing data like this:

{
    firstName: 'John',
    address: {
        street: 'bumpyroad',
        postalCode: '123',
    }
}
{
    firstName: 'Martin',
    address: {
        street: 'anotherroad',
        postalCode: '456',
    }
}

I want to select the street and preserve the json structure. The desired output is

{
    address: {
        street: 'bumpyroad'
    }
}
{
    address: {
        street: 'anotherroad'
    }
}

In Mongo the same can be done with {$projection:{"address.street":1}}. How can i achieve this with jsonb in postgres?

I tried SELECT myjsonbcolumn->'address'->'street' FROM "sometable" but this returns a flat list of strings and does not preserve the parent structure.

Answer 1

You can build the desired structure with jsonb_build_object(), incorporating the value in it: demo

SELECT jsonb_pretty(
            jsonb_build_object( 
                 'address'
                 ,jsonb_build_object(
                       'street'
                       ,myjsonbcolumn#>'{address,street}')))
FROM "sometable";

jsonb_pretty
{     "address": {         "street": "bumpyroad"     } }
{     "address": {         "street": "anotherroad"     } }