How to do text query from within json

Question

I need to search for records that have an outcome of "Achieved". The data is a JSON object that is in a text column in Postgres 9.4.

I tried using:

notes.data similar to '%"Legal"%' AND 
notes.data similar to '%"Achieved"%' AND
notes.data similar to '%"Education"%' AND
notes.data similar to '%"Achieved"%'

However, the instance of the word "Achieved" does not necessarily match in the same place as "Legal". As you can see in a sample data below, in the Goals Array section, there may be updates on progress - the ## comments are mine to illustrate. (You need to remove those to get valid JSON format!)

The query pulls the status "Legal" fine, but the second condition "Achieved" may not match as the most current status update.

I have read on text queries in JSON, but I am at a loss with this structure. I put it in jsonlint.com, and it validates as correct JSON. Could someone assist?

Here is the complete query followed by a sample of the data:

SELECT 
  clients.name_lastfirst_cs, 
  clients.client_id, 
  notes.date_service, 
  services.code, 
  services.name, 
  notes.data, 
  notes.zrud_template,
  staff.staff_name_cs

FROM 
  public.clients, 
  public.staff, 
  public.notes, 
  public.services
WHERE 
  clients.zzud_client = notes.zrud_client AND
  notes.zrud_staff = staff.zzud_staff AND
  notes.zrud_service = services.zzud_service AND
  notes.data similar to '%Legal%' AND notes.data similar to '%Achieved%' AND
  notes.data similar to '%Substance Abuse%' AND notes.data similar to '%Achieved%'AND
  services.code = '000502' AND
  notes.date_service BETWEEN  '07/01/2014' AND '04/15/2015'
ORDER BY clients.name_lastfirst_cs ASC;

Example value in notes.data:

{
    "DxArray": [
        [
            [
                "Axis I",
                "305.20",
                "Cannabis Abuse ",
                1,
                "4F9E9DC8-D2ED-433A-A129-5696B34A866C"
            ]
        ],
        [
            [
                "Axis I",
                "304.20",
                "Cocaine Dependence In Full Remission ",
                2,
                "37BA3F7C-B376-4DFD-82BE-04F26E3A9F2A"
            ]
        ],
        [
            [
                "Axis II",
                "799.9",
                "Deferred",
                3,
                "DF80F337-DEF6-4265-9CBA-0F4ECA3E1A4D"
            ]
        ],
        [
            [
                "Axis III",
                null,
                "Chronic Pain per client report. ",
                4,
                "3C49E6EC-9C95-4CA7-8005-DF70D071F5F1"
            ]
        ],
        [
            [
                "Axis IV",
                null,
                "legal issues ",
                5,
                "C3114B75-ECDB-40AB-AD37-544D341549BA"
            ]
        ],
        [
            [
                "Axis V",
                null,
                "GAF=60",
                6,
                "3C1225B3-1B5B-4F4C-A75A-1A2E2F46AAC2"
            ]
        ],
        [
            [
                "Axis",
                "Code",
                "Diagnosis"
            ]
        ]
    ],
    "update": "03/19/2015",                             ## date of update
    "": "",
    "header_HTML": "",
    "Barriers": "Violation ",
    "DOB": "01/15/1980",
    "previewBounds": "",
    "p0": 27,
    "zrud_template": "82552FEB-8408-4A2D-81CF-564CC04108F8",
    "fv_location": "Office/Agency",
    "zzud_client": "07863B38-3793-49B5-A3C4-CB2186AAA48E",
    "name_first": "Roger",
    "Date": "03/10/2014",                                 ## date of service plan
    "Preferences": "\"I want to get finished with probation.",
    "ID": "RABR010170",
    "gender": 1,
    "fv_servicecode": "000502",
    "zrud_service": "D303BD9A-FA08-45DB-8CB4-BCE68FF83AE6",
    "zzud_staff": "884AC915-0D3A-4826-B248-11498323DA21",
    "fv_servicename": "Service Plan",
    "axis_HTML": "",
    "goals_HTML": "",
    "zrud_link_service": "6171D722-632E-4BEC-B521-F598C1888040",
    "is_cloned": "F8158F4B-F269-4BC9-AA20-48872F59C2E4",
    "previous_plans": {
        "1": {},
        "2": {},
        "3": {},
        "4": {},
        "5": {},
        "6": {},
        "7": {}
    },
    "GoalsArray": [                   ## beginning of goals
        [
            [
                "Legal",               ## goal 1
                " He will not engage in criminal activity.",
                " Comply with all requirements of probation. No new crime or probation violation. ",
                " Frequency: At least once per month and as needed.",
                " Compliance with Care Manager Plan and all probation requirements.",
                "Ted Therapist, CM\n\nClient\n\nProbation Officer ",
                99,
                "03/10/2014"         ## date of goal creation
            ],
            [
                [
                    "09/06/2014",     ## new target date
                    "03/19/2015",     ## date updated
                    "Ongoing",        ## status   This is repeated for each review
                    "No new legal issues per client report. "
                ],
                [
                    "09/06/2014",
                    "01/05/2015",
                    "Ongoing",
                    "Client reported attending court for probation violation due to lack of payment. "
                ],
                [
                    "09/06/2014",
                    "10/23/2014",
                    "Achieved",
                    "No new legal issues per client report. "
                ],
                [
                    "09/06/2014",
                    "08/28/2014",
                    "Ongoing",
                    "No new legal issues per client report. "
                ],
                [
                    "09/06/2014",
                    "07/30/2014",
                    "Ongoing",
                    "No legal charges per client report. "
                ],
                [
                    "09/06/2014",
                    "06/05/2014",
                    "Ongoing",
                    "No new legal issues per client report. "
                ],
                [
                    "09/06/2014",
                    "05/08/2014",
                    "Ongoing",
                    "No legal issues per client report. "
                ],
                [
                    "09/06/2014",
                    "03/10/2014",
                    "Ongoing",
                    "Initial creation."
                ],
                [
                    "tDate",
                    "rDate",
                    "Sts",
                    "Just"
                ]
            ]
        ],
        [
            [
                "Substance Abuse",                          ## goal 2
                " Copy: Successfully complete Intensive Outpatient Treatment (IOPT) program.",
                " Refer client to IOPT Treatment program.",
                " Frequency: At least once per month and as needed.",
                " Care Manager and He will discuss progress as it relates to Outpatient Treatment.",
                "Ted Therapist, CM\n\nClient \n\nTreatment Provider ",
                104,
                "03/10/2014"                                ## date created
            ],
            [
                [
                    "09/06/2014",
                    "03/19/2015",
                    "Discontinued",                              ## status
                    "Client reported 2/2015 as last date of use for cannabis. "
                ],
                [
                    "09/06/2014",
                    "01/05/2015",
                    "Ongoing",
                    "Client denies subtance use over last couple months. "
                ],
                [
                    "09/06/2014",
                    "10/23/2014",
                    "Ongoing",
                    "Client reports smoking cannabis 3 weeks ago due to family issues.  He reports testing 

positive at office meeting today. "
                ],
                [
                    "09/06/2014",
                    "08/28/2014",
                    "Ongoing",
                    "No substance use per client report. "
                ],
                [
                    "09/06/2014",
                    "07/30/2014",
                    "Ongoing",
                    "Client reports using marijana one month ago. "
                ],
                [
                    "09/06/2014",
                    "06/05/2014",
                    "Ongoing",
                    "No substance use per client report. "
                ],
                [
                    "09/06/2014",
                    "05/08/2014",
                    "Ongoing",
                    "No substance use per client report. "
                ],
                [
                    "09/06/2014",
                    "03/10/2014",
                    "Ongoing",
                    "Initial creation."
                ],
                [
                    "tDate",
                    "rDate",
                    "Sts",
                    "Just"
                ]
            ]
        ],
        [
            [
                "Domain",
                "Goal",
                "Interventions",
                "Frequency",
                "Progress",
                "Clinician",
                "Base",
                "Created"
            ],
            [
                "tDate",
                "rDate",
                "Sts",
                "Just"
            ]
        ]
    ],
    "Strengths": "Supportive family \ngood person \nhard worker",
    "zzud_group": "E8163487-E654-4D07-A13C-C2CAEB4EDB24",
    "noReviewDate": "09/06/2014",
    "newPlan": "",
    "is_saved": "33E8C6A4-9DC8-451F-897F-EE7597619FFF",
    "name_last": "Rabbit"
}

have you tried using proper json operators? http://www.postgresql.org/docs/9.4/static/functions-json.html — murison, Apr 19 '15 at 19:44
murison - thanks for the link. I looked at this but cannot figure out how to go past the Legal element. There is nothing to identify the word "Ongoing" in the next set of brackets. This is my dilemma. — Detox, Apr 19 '15 at 20:07
Any particular reason for `data is a json object that is in a text column`? You are using pg 9.4, why don't you store it in a [`jsonb`](http://www.postgresql.org/docs/current/interactive/datatype-json.html) column? — Erwin Brandstetter, Apr 19 '15 at 22:41
Erwin, The database was constructed with postgresq-9.1 and I have updated it. I had not changed any structure as I thought initially I could search like a text string, but that proved very unreliable. I will change it on a test machine. Do you have any idea how to do a query on json like this? — Detox, Apr 19 '15 at 23:09

Erwin Brandstetter · Answer 1 · 2021-03-30T23:35:46.523

Basics

Store JSON data in a jsonb column. Enforces integrity, requires less space on disk and allows indexes. Also, for most useful operations (like your query) you need to cast to json or jsonb anyway.
- How do I query using fields inside the new PostgreSQL JSON datatype?
Don't use ~~SIMILAR TO~~. It's no good.
- Using SIMILAR TO for a regex?
Rather use ISO 8601 date format which is unambiguous with any locale or DateStyle setting. ('2015-04-15', not ~~'04/15/2015'~~)
- Selecting records between two timestamps
Use explicit JOIN syntax and table aliases for more readable SQL code.

Query

Your query reformatted and with LIKE instead of SIMILAR TO, but otherwise still unchanged (doesn't work, yet!):

SELECT c.name_lastfirst_cs
     , c.client_id
     , n.date_service
     , s.code
     , s.name
     , n.data
     , n.zrud_templat
     , t.staff_name_cs
JOIN   public.services s 
JOIN   public.notes    n ON n.zrud_service = s.zzud_service
FROM   public.clients  c ON c.zzud_client  = n.zrud_client
JOIN   public.staff    t ON t.zzud_staff   = n.zrud_staff
WHERE  s.code = '000502'
AND    n.date_service BETWEEN '2014-07-01' AND '2015-04-15'

AND    n.data LIKE '%Legal%'             -- problematic predicates
AND    n.data LIKE '%Achieved%'
AND    n.data LIKE '%Substance Abuse%'
AND    n.data LIKE '%Achieved%'

ORDER  BY c.name_lastfirst_cs;

Neither current query nor explanation clarify what to match exactly. Nested JSON arrays are hard to work with unless you know the position in the array. Not impossible, but hard.

Assuming jsonb data type. Change the data type if you have not done it yet:

ALTER TABLE notes ALTER COLUMN data TYPE jsonb USING data::jsonb;

If positions don't change, "Legal" is always the first element in the 3rd level of the nested array and "Substance Abuse" is the second, and the latest entry comes first and positions in the arrays are fixed. IOW: with the example you provided, this would work:

AND    n.data #>> '{GoalsArray,0,0,0}'   = 'Legal'
AND    n.data #>> '{GoalsArray,0,1,0,2}' = 'Achieved'
AND    n.data #>> '{GoalsArray,1,0,0}'   = 'Substance Abuse'
AND    n.data #>> '{GoalsArray,1,1,0,2}' = 'Achieved'

db<>fiddle here
_{Old sqlfiddle}

Else you'll have to unnest JSON arrays (recursively) with jsonb_array_elements() and write more complex queries. WITH ORDINALITY will be most useful to attach unique numbers to unnested elements:

PostgreSQL unnest() with element number

How to unnest JSON arrays:

You might be able to use indices:

Index for finding an element in a JSON array

Erwin - Thank you so much for you help! I will begin this process and follow your advice. The positions of "Legal" etc will change with creation of each entry, as staff enter in goals unique to a specific individual and the order of entry is not constant. This means I will be doing a lot of reading (which is good). I will keep you posted on how it goes. Thanks again! — Detox, Apr 20 '15 at 01:17

How to do text query from within json

1 Answers1

Basics

Query