query json data in postgres 9.3

Question

I have read the questions regarding json columns and apparently just not understanding.

Here is excerpt of a postgresql query using a json column My problem is:

I can query a top level for data ( referralReasons ) But when I try to query data from a lower level ( TASCReferral .... "referralComments" I get blank results I realize I am not writing the query correctly, but I cannot find understandable instructions on how.

Thanks for any help

Below this is the json for reference

-------------- query ---------------------------

 SELECT 
  groups.name AS group, 
  (to_char (referrals."referraldate", 'MM/DD/YY'))AS DateReferred,
  referrals."referral_age",
  referrals."name" AS Offender,
  referrals.po_name AS Officer,
 -- referrals.jsondata->'referralReasons' AS po_comment,
 referrals.jsondata->'referralComments' AS po_comment,
 referrals.visibility_flag

FROM
groups, referrals
WHERE .........

--------- json data taken from referrals.jsondata column---------

{
    "pendingCharges": [],
    "residence": {
        "zip": "12345",
        "phone": "(252)555-1212",
        "county": "BEAUFORT",
        "street": "1313 MOCKING BIRD LN",
        "state": "NC",
        "city": "SOMETOWN"
    },
    "dates": {
        "referralDate": "2015-12-08",
        "convictionDate": "2011-10-20",
        "birthDate": "1909-09-16"
    },
    "html": {
        "zrud_group": "TEST",
        "zzud_referral": "15AC82D5-E743-40A2-8553-ER765DKOPIT11"
    },
    "TASCReferral": {
        "referralKey": "ABCD-EFG-HITK",
        "phone": {},
        "charges": "LARCENY",
        "conviction": "019 years 11 months 10 days",
        "officerName": "BUTLER, SMEDLEY",
        "DrugTestSummaryLink": "https.GOHERE.COM",
        "referralComments": "Appointment Date: Week of 12/08/2015   -- This offender is an out of state case.  He tested positive today for cocaine.",
        "officerEmail": "[email protected]",
        "supervisionLevel": "L3",
        "gangName": " ",
        "RNASummaryLink": "https://GOTHERE.COM",
        "punishmentType": "NON-N.C. OFF.",
        "OPUSID": "9876543",
        "docketNumber": "CR00000000",
        "officerPhone": "(252)666-8888",
        "name": "CLIENT TEST",
        "age": "18",
        "gangAffiliated": "false",
        "countyOfConviction": "OTHER",
        "gender": "Male",
        "supervisionType": "PRO: PROBATION",
        "TASCOffice": "TESTOFFICE"
    },
    "referralReasons": [
        "Court-Ordered Assessment",
        "Baseline Admission or Positive Drug Screen",
        "Offender admits to using/positive for: cocaine - Date of last use: 10/09/2015"
    ]

json is not valid - add closing }

Dzmitry Savinkou
– Dzmitry Savinkou

2015-12-09 18:44:38 +00:00
Commented Dec 9, 2015 at 18:44 — Dzmitry Savinkou
– Dzmitry Savinkou, Commented Dec 9, 2015 at 18:44

Dzmitry Savinkou · Accepted Answer · 2015-12-09 18:51:11Z

1

Assuming that you have your data column with JSON, you can use -> (get JSON object field) and ->> (get JSON field as TEXT) to go over json structure, relevant documentation is here:

SELECT jt.data->'TASCReferral'->>'referralComments' 
FROM json_test jt;

answered Dec 9, 2015 at 18:51

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

1 Comment

Detox Over a year ago

Dmitry - Thanks so much! I had the information a year of so ago, and could not find it. Mr google was little help. I just ran the query with your changes and it worked great. Now I need to re-create a "How-Do-I" doc and put in safe place. Again, you are a life saver!

Collectives™ on Stack Overflow

query json data in postgres 9.3

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