Столбец Postgres со строковыми значениями Unicode

avatar
ezeagwulae
7 апреля 2018 в 22:51
858
1
1

у меня есть столбец query_params с типом TEXT, но значения хранятся в виде строки 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 и конвертировать в столбцы:

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

Спасибо за любую помощь.

Источник
klin
7 апреля 2018 в 23:16
0

Является ли текст примера одной строкой или двумя строками?

ezeagwulae
8 апреля 2018 в 00:08
0

@klin пример находится в одной строке. я обновлю описание для ясности

Ответы (1)

avatar
klin
8 апреля 2018 в 00:52
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.

Прочитайте о регулярных выражениях POSIX в документации.

Объяснение выражения регулярного выражения см. на regex101.com.

ezeagwulae
8 апреля 2018 в 02:56
0

большое спасибо... кажется, что SqlFiddle работает, но когда я попробовал ваше решение, он возвращает ошибку: [22P02] ERROR: invalid input syntax for type json Detail: Token "u" is invalid. Кроме того, если вы можете объяснить, что происходит в первой части функции regexp_replace, это было бы огромной помощью.

klin
8 апреля 2018 в 12:19
0

Это означает, что примерный текст не совсем совпадает с фактическим. Смотрите обновленный ответ.

ezeagwulae
8 апреля 2018 в 17:32
0

Привет. Спасибо. дополнительная информация о регулярном выражении была очень полезна для изучения объяснения выражения.