Мне нужно найти записи с результатом "Achieved". Данные представляют собой объект JSON, который находится в столбце text в Postgres 9.4.
Я пытался использовать:
notes.data similar to '%"Legal"%' AND
notes.data similar to '%"Achieved"%' AND
notes.data similar to '%"Education"%' AND
notes.data similar to '%"Achieved"%'
Однако экземпляр слова "Achieved" не обязательно соответствует тому же месту, что и "Legal". Как вы можете видеть в примере данных ниже, в разделе Goals Array могут быть обновления о прогрессе — комментарии ## являются моими иллюстрациями. (Вам нужно удалить их, чтобы получить действительный формат JSON!)
Запрос получает статус "Legal" в порядке, но второе условие "Achieved" может не совпадать с последним обновлением статуса.
Я читал текстовые запросы в JSON, но я в недоумении от этой структуры. Я поместил его на jsonlint.com, и он подтвердил правильность JSON. Может ли кто-нибудь помочь?
Вот полный запрос, за которым следует образец данных:
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;
Пример значения в 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"
}
data is a json object that is in a text column? Вы используете pg 9.4, почему бы вам не сохранить его вjsonbстолбец? - person Erwin Brandstetter schedule 20.04.2015