CODEWITHSUNDEEP
regexjsonpostgresqlsubstring
Florian
Florian

Reputation: 1541

Using substring to get a JSON value in PostgreSQL

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.

Upvotes: 1

Views: 5327

Answers (1)

Alex Filipovici
Alex Filipovici

Reputation: 32571

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

Upvotes: 6

Related Questions