current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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.

share|improve this question
    
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 Jan 6 at 14:57

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.