sql - 如何从 json 中进行文本查询

Question

我需要搜索结果为 "Achieved" 的记录。数据是一个 JSON 对象，位于 Postgres 9.4 中的 text 列中。

我尝试使用:

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 格式!)

查询提取状态 “合法” 正常，但第二个条件 “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"
}

Answer 1

基础知识

• 将 JSON 数据存储在 jsonb 中 列。强制完整性，需要更少的磁盘空间并允许索引。此外，对于大多数有用的操作(如您的查询)，您无论如何都需要转换为 json 或 jsonb。

  • How do I query using fields inside the new PostgreSQL JSON datatype?

• 不要使用 SIMILAR TO 。这不好。

  • Using SIMILAR TO for a regex?

• 最好使用 ISO 8601 日期格式，它在任何语言环境或 DateStyle 中都是明确的环境。 ('2015-04-15'，不是 '04/15/2015' )

  • Selecting records between two timestamps

• 使用显式 JOIN 语法和表别名以获得更具可读性的 SQL 代码。

查询

您的查询已重新格式化并使用 LIKE 而不是 SIMILAR TO，但其他方面仍未改变(还不起作用!):

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;

当前的查询和解释都没有阐明要完全 匹配什么。除非您知道数组中的位置，否则很难使用嵌套的 JSON 数组。不是不可能，而是很难。

假设 jsonb 数据类型。如果您还没有更改数据类型:

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

如果位置不变，"Legal" 始终是嵌套数组第 3 层中的第一个元素，"Substance Abuse" 是第二个，最新的条目在前，数组中的位置是固定的。 IOW:对于您提供的示例，这将起作用:

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
<子>旧sqlfiddle

否则您必须使用jsonb_array_elements() 取消嵌套JSON 数组(递归)并编写更复杂的查询。 WITH ORDINALITY 最适合将唯一编号附加到未嵌套的元素:

• PostgreSQL unnest() with element number

如何取消嵌套 JSON 数组:

• How to turn json array into postgres array?
• PostgreSQL joining using JSONB

您可以使用索引:

• Index for finding an element in a JSON array