从Postgresql表中的columns字段提取键和值

Question

id   |                   columns                    |         timestamp          | query_id             |               task_id
-------+----------------------------------------------+----------------------------+----------------------+---------------------------
1 | {"uid": "112", "name": "redis-server"}       | 2018-07-18 18:45:39.045387 |                    1 |                         2
2 | {"uid": "0", "name": "celery"}                 | 2018-07-18 18:45:39.047671 |                    1 |                         2
3 | {"uid": "111", "name": "post"}           | 2018-07-18 18:45:39.048218 |                    1 |                         2
4 | {"uid": "111", "name": "post"}           | 2018-07-18 18:45:39.048732 |                    1 |                         2

希望从json中提取UID&通过查询语法的NAME

Answer 1

您可以使用JSON运算符->>。即：

select *, "columns"->>'uid' as uid, "columns"->>'name' as name
from myTable;