设置示例：

create table my_table(id int, data jsonb);
insert into my_table values
(1,
$${
   "type": "a type",
   "form": "a form",
   "contact": {
       "name": "a name",
       "phone": "123-456-78",
       "type": "contact type",
       "parent": {
           "id": "444",
           "type": "parent type" 
           } 
    }
}$$);

递归查询对在任何级别上找到的每个json对象执行jsonb_each()。新的键名包含从根目录开始的完整路径：

with recursive flat (id, key, value) as (
    select id, key, value
    from my_table,
    jsonb_each(data)
union
    select f.id, concat(f.key, '.', j.key), j.value
    from flat f,
    jsonb_each(f.value) j
    where jsonb_typeof(f.value) = 'object'
)
select id, jsonb_pretty(jsonb_object_agg(key, value)) as data
from flat
where jsonb_typeof(value) <> 'object'
group by id;

 id |                   data                   
----+------------------------------------------
  1 | {                                       +
    |     "form": "a form",                   +
    |     "type": "a type",                   +
    |     "contact.name": "a name",           +
    |     "contact.type": "contact type",     +
    |     "contact.phone": "123-456-78",      +
    |     "contact.parent.id": "444",         +
    |     "contact.parent.type": "parent type"+
    | }
(1 row)

如果要获得此数据的平面视图，可以使用此答案Flatten aggregated key/value pairs from a JSONB field?中描述的函数create_jsonb_flat_view()
您需要使用扁平化的jsonb创建一个表（或视图）：

create table my_table_flat as 
-- create view my_table_flat as 
with recursive flat (id, key, value) as (
-- etc as above
-- but without jsonb_pretty()

现在您可以使用表中的函数：

select create_jsonb_flat_view('my_table_flat', 'id', 'data');

select * from my_table_flat_view;

 id | contact.name | contact.parent.id | contact.parent.type | contact.phone | contact.type |  form  |  type  
----+--------------+-------------------+---------------------+---------------+--------------+--------+--------
  1 | a name       | 444               | parent type         | 123-456-78    | contact type | a form | a type
(1 row)

该解决方案适用于Postgres 9.5+，因为它使用了此版本中引入的jsonb函数。如果您的服务器版本较旧，强烈建议无论如何升级Postgres以有效地使用jsonb。

使用函数jsonb_unnest_recursive()
质询：

select cardinality(path) as level,
       array_to_string(path, '.') as path,
       value
from jsonb_unnest_recursive($${
  "type": "a type",
  "form": "a form",
  "contact": {
    "name": "a name",
    "phone": "123-456-78",
    "type": "contact type",
    "numbers": [11,22,33],
    "parent": {
      "id": "444",
      "type": "parent type"
    }
  }
}$$::jsonb) as t;

结果：

• Stackoverflow无法将此减价解析到表 *：（

| level | path | value |
| :--- | :--- | :--- |
| 1 | form | "a form" |
| 1 | type | "a type" |
| 2 | contact.name | "a name" |
| 2 | contact.type | "contact type" |
| 2 | contact.phone | "123-456-78" |
| 3 | contact.parent.id | "444" |
| 3 | contact.parent.type | "parent type" |
| 3 | contact.numbers.0 | 11 |
| 3 | contact.numbers.1 | 22 |
| 3 | contact.numbers.2 | 33 |