Search in JSON with Postgres without knowing all keys-CodePudding

I'm trying to search in a JSON using Postgres. The JSON looks like that:

  "some key": {
    "city": "Chicago",
    "id": "",
    "color": "",
    "size": ""
  },
  "a different key": {
    "city": "San Francisco",
    "id": null,
    "shape": "",
    "height": ""
  }

I don't know what can the names of the first level keys (that's why I called them "some key" and "a different key" in the example above). I do know that they can be different from one another.

I want to extract all the values of the "city" key, Chicago and San Francisco in the example above. I guess it's something like that but this one didn't work:

(table_name.row_name-> * ->> 'city') as city_name

(I know that the city is always in the second level on the JSON, but can occur multiple times)

CodePudding user response：

You can iterate over the keys:

select t.some_column, 
       x.item -> 'city' as city_name
from the_table t
   cross join jsonb_each(t.the_column) as x(item)

This returns each city as a new row together with the other columns of that table.

The above assumes your column is defined as jsonb (which it should be). If it isn't you need to use json_each() instead