substring match in redshift database

Question

I have a redshift table "person" in which a particular column has data something like this

[{"attributeName":"name","attributeMetadata":null,"attributeValue":"KitchenAid - 7-Speed Hand Mixer - White","attributeImageType":"PRODUCT","attributeStatusCodes":[]},
{"attributeName":"title","attributeMetadata":null,"attributeValue":"KitchenAid","attributeImageType":"PRODUCT","attributeStatusCodes":[]},

{"attributeName":"address","attributeMetadata":null,"attributeValue":"address","attributeImageType":"PRODUCT","attributeStatusCodes":[]},

{"attributeName":"PIN CODE","attributeMetadata":null,"attributeValue":"32110","attributeImageType":"IMG","attributeStatusCodes":[]}]

I would like to extract only the dictionary/json/substring containing PIN CODE (see below)

{"attributeName":"PIN CODE","attributeMetadata":null,"attributeValue":"32110","attributeImageType":"IMG","attributeStatusCodes":[]}

I tried the following query and it is giving the following error

select distinct regexp_substr(attributes,'.*({.*?"attributeName":"PIN CODE".*?}).*') from person ;

ERROR:  Invalid content of repeat range
DETAIL:  
  -----------------------------------------------
  error:  Invalid content of repeat range
  code:      8002
  context:   T_regexp_init
  query:     528401
  location:  funcs_expr.cpp:130
  process:   query2_40 [pid=12603]
  -----------------------------------------------

I guess the problem is occurring because of multiple attributeName in a single column. Is their a way to achieve the desired result.

You should escape curly braces as \{, otherwise it's assumed as a quantifier like \w{2,5}. That's why it throws the error "Invalid content of repeat range" — Mariano
– Mariano, Commented Nov 4, 2015 at 8:26

Ivan · Accepted Answer · 2015-11-04 07:52:16Z

2

I am not sure if I understood you correctly, but you can try to use LIKE:

    select * from person where attributes LIKE '%"attributeName":"PIN CODE"%';

answered Nov 4, 2015 at 7:52

Ivan

5121 gold badge6 silver badges16 bronze badges

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

1 Comment

Anurag Sharma Over a year ago

@IvanNo I want the subtring {"attributeName":"PIN CODE","attributeMetadata":null,"attributeValue":"32110","attributeImageType":"IMG","attributeStatusCodes":[]}. The string above(in the question) is in a single column

Collectives™ on Stack Overflow

substring match in redshift database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related