How to select only part of a JSON string

Question

I have a field that returns

{"nextPONumber":"3","poReportFooter":"This is the footer text for a purchase order report"}

I want to pull just the

"This is the footer text for a purchase order report"

SELECT
  modulesetting.value AS footer_value
FROM modulesetting
WHERE modulesetting.key='purchasing'

the field is footer_value which returns {"nextPONumber":"3","poReportFooter":"This is the footer text for a purchase order report"} — Tim Cluff
– Tim Cluff, Commented Mar 23, 2017 at 16:07

amphetamachine · Accepted Answer · 2017-03-23 16:08:17Z

1

Use the built-in JSON functionality:

select
    modulesetting.value::json->>'poReportFooter'
from
    modulesetting
WHERE modulesetting.key='purchasing';

answered Mar 23, 2017 at 16:08

amphetamachine

31k12 gold badges68 silver badges74 bronze badges

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

Comments

Vao Tsun · Accepted Answer · 2017-03-23 16:06:53Z

0

a wild guess:

SELECT
  modulesetting.value->>'poReportFooter' AS footer_value
FROM modulesetting
WHERE modulesetting.key='purchasing'

answered Mar 23, 2017 at 16:06

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges