How extract jsonb column in fields by key

I’m using postgresql and postgis plugin.

I have stored data with this scheme: table_id is primary key, properties is a jsonb and geom as geometry(GeometryZ,4326);

If I make this request: select table_id, properties, geom from nametable return all info with table_id as integer, properties as jsonb and geom as geometry(GeometryZ,4326).

I would like a query where properties return table_id, a list of column where name of column is the key of value and his value and geom. For example, if properties has name and density return a response like this: table_id | name | density | geom |, but I dont know the name of properties so I think that should be a query inside that query that get name of keys.

The closest I’ve been to get it, its with this query: select jsonb_object_keys(properties) as key from nametable;

Thanks

EDIT:

First, I have stored a field as jsonb in my postgressql database, so I would like extract that jsonb to columns. But have stored differents tables that contains differents properties into jsonb column.

So, the idea is get a query where select table_id, properties(extracted in multiple columns) and geom.

1 – With this I have the name of keys: select jsonb_object_keys(properties) as key from nametable group by key;

2 – With keys get in column all values of each key.

3 – Return a query where when I call it, return me table_id, column of properties extracted from jsonb, geom from nametable;

My problem is that I dont know how generate that query with sub-queries.

Answer

Have you tried using a CTE?

WITH j AS (
  SELECT 
    table_id, 
    properties->>'name' AS name,
    properties->>'density' AS density,
    geom 
  FROM t)
SELECT * FROM j WHERE j.name = 'Alabama';

Advertisement

Answer