Postgres column with string unicode values

Question

i have a column query_params with type TEXT but the values are stored as a string unicode. each value in the column is prefixed with a u and i'm struggling to remove it

Is there a way to remove the u from the values and convert the values dictionary to columns?

for example, the query SELECT query_params FROM api_log LIMIT 2 returns two rows

{
  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'
}

is it possible to handle unicode in postgres and convert to columns:

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

Thanks for any help.

@klin the example is in a single row. i'll update the description for clarity — ezeagwulae
– ezeagwulae, Commented Apr 8, 2018 at 0:08

klin · Accepted Answer · 2018-04-10 20:54:20Z

1

You should remove u letters and replace single quotes with double ones to get properly formatted json. Then you can use the ->> operator to get its attributes:

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;

Test the solution in SqlFiddle.

Read about POSIX Regular Expression in the documentation.

Find an explanation of the regexp expression in regex101.com.

edited Apr 10, 2018 at 20:54

answered Apr 8, 2018 at 0:52

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ezeagwulae Over a year ago

thanks so much..the SqlFiddle seems to work but when i tried your solution in it returns an error: [22P02] ERROR: invalid input syntax for type json Detail: Token "u" is invalid. Also, if you can explain whats happening in the first part of the regexp_replace function that would be a huge help.

klin Over a year ago

This means that the example text is not exactly the same as the actual one. See the updated answer.

ezeagwulae Over a year ago

Hey. thanks so much. the additional information on regexp was super helpful to learn explanation of the expression.

Collectives™ on Stack Overflow

Postgres column with string unicode values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related