Using substring to get a JSON value in PostgreSQL

Question

I've got a table field "data" with the following json:

[{"reason": "Other", "bla": 12345, "amount": "34.00", "moredata": [23, 22], "date": "2014-01-02T01:55:28.646364+00:00", "message": "Bla", "applied_to": "client"}]

And I'd like to get only the value of the json value of amount, so 34.00 in the case above in PostgreSQL.

So far I have:

substring(data from '%#"_amount_: ___.___#"%' for '#'),

Sadly this gives me "amount":"34.00", instead of just 34.00. It also doesn't work if the amount value is 9.00 or 102.00.

Any help would be appreciated.

Why not just use the built-in json support, which has a proper json parser? Or use a PL like plperl and a json lib for it? String-fiddling will be fragile and unreliable. — Craig Ringer
– Craig Ringer, Commented Jan 6, 2014 at 14:57

Alex Filipovici · Accepted Answer · 2014-01-06 15:05:11Z

6

Try:

substring(data from '(?:"amount": ")([\d\.]*)')

See it working here and here's what it does:

NODE                     EXPLANATION
--------------------------------------------------------------------
  (?:                      group, but do not capture:
--------------------------------------------------------------------
    "amount": "              '"amount": "'
--------------------------------------------------------------------
  )                        end of grouping
--------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------
    [\d\.]*                  any character of: digits (0-9), '\.' (0
                             or more times (matching the most amount
                             possible))
--------------------------------------------------------------------
  )                        end of \1

edited Jan 6, 2014 at 15:05

answered Jan 6, 2014 at 14:55

Alex Filipovici

32.8k7 gold badges60 silver badges81 bronze badges

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

1 Comment

pbnelson Over a year ago

Thanks a million for this. I had to make a slight modification in my own case, to use '\w' instead of '\d' for all "word" characters instead of digits only. Also my JSON looked a wee bit different, so I had to remove the space and colon. Here's what mine looked like: "HasOptedOutOfEmail":false which I parsed using this regex '(?:"HasOptedOutOfEmail":)([\w\.]*)'

Collectives™ on Stack Overflow

Using substring to get a JSON value in PostgreSQL

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