json - 具有字符串 unicode 值的 Postgres 列

Question

我有一个类型为 TEXT 的列 query_params 但值存储为字符串 unicode。列中的每个值都以 u 为前缀，我正在努力将其删除

有没有办法从值中删除 u 并将值字典转换为列？

例如，查询 SELECT query_params FROM api_log LIMIT 2 返回两行

{
  u'state': u'CA',
  u'page_size': u'1000',
  u'market': u'Western',
  u'requested_at': u'2014-10-28T00:00:00+00:00'
},
{
  u'state': u'NY',
  u'page_size': u'1000',
  u'market': u'Eastern',
  u'requested_at': u'2014-10-28T00:10:00+00:00'
}

是否可以在 postgres 中处理 unicode 并转换为列:

state | page_size | market   | requested_at
------+-----------+----------+---------------------------
CA    | 1000      | Western  | 2014-10-28T00:00:00+00:00
NY    | 1000      | Eastern  | 2014-10-28T00:10:00+00:00

感谢您的帮助。

Answer 1

您应该删除 u 字母并将单引号替换为双引号以获得格式正确的 json。然后你可以使用 ->> 运算符来获取它的属性:

select 
    v->>'state' as state,
    v->>'page_size' as page_size,
    v->>'market' as market,
    v->>'requested_at' as requested_at
from (
    select regexp_replace(query_params, 'u\''([^\'']*)\''', '"\1"', 'g')::json as v
    from api_log
    ) s;

测试 SqlFiddle. 中的解决方案

阅读 the documentation. 中的 POSIX 正则表达式

在 regex101.com. 中找到正则表达式的解释