Postgres query inside a json Field

glennbtn · 01-13-2024, 04:50 AM

Hi All

I need to get some data out of our phone system. All the calls are written to the database and the json field which contains the data I need.

Can anyone advise how I would do a db search in the json field for all the "digits_dialed": "2"

I can do the normal select * from v_xml_cdr for basic stuff but this has me stumped as there is so much data in this field in each column

Thanks

nateb · 01-13-2024, 01:23 PM

Assuming the digits_dialed field is at the top level of your JSON column (and that your column is JSON-typed), you can use this to get the ID of all the fields where digits_dialed is the string '2'.

Code:

WITH some_table(id, json_field) AS (
  VALUES
    (1::integer, '{"digits_dialed": "2"}'::json),
    (2, '{"digits_dialed": "3"}'),
    (3, '{"digits_dialed": "2"}')
)
SELECT
  id,
  json_field->>'digits_dialed' as digits_dialed
FROM some_table
WHERE json_field->>'digits_dialed' = '2';

Fiddle.

If digits_dialed is nested in a JSON object, you'll need #>> with e.g. (json_field#>>'{path,to,digits_dialed}') (docs).