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

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

转载作者：行者123 更新时间：2023-11-29 13:28:11

我需要搜索结果为 "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"
}

最佳答案

基础知识

将 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 数组:

您可以使用索引:

Index for finding an element in a JSON array

关于sql - 如何从 json 中进行文本查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/29734676/

文章推荐： php - 使用 php 文件连接 wordpress 上的表单页面

文章推荐： ios - Xcode 4.5 (iOS) - 链接项目(库和应用程序)

文章推荐： php - 循环到子级的 FINITIE 深度

文章推荐： python - 我可以批量递增值以减少数据库写入吗？

javascript - 我需要将文本放在一个中，它位于一个 Div 中，该 Div 位于另一个 Div 中，该 Div 位于另一个 Div 中
我需要将文本放在中在一个 Div 中，在另一个 Div 中，在另一个 Div 中。所以这是它的样子: #document Change PIN
html - 两个背景图像。一个在 HTML 中，一个在 BODY 中。在 Firefox 中，主体图像未呈现
奇怪的事情发生了。我有一个基本的 html 代码。 html，头部， body 。(因为我收到了一些反对票，这里是完整的代码) 这是我的CSS: html { backgroun
ios - 将图像从 asset.xcassets 加载到 imageArray 中，并将其动态加载到 UIImageView 中，该 UIImageView 存在于 UICollectionView 中 - swift
我正在尝试将 Assets 中的一组图像加载到 UICollectionview 中存在的 ImageView 中，但每当我运行应用程序时它都会显示错误。而且也没有显示图像。我在ViewDidLoa
linux - 在 BASH 中，我需要根据 perl 脚本的输出更改一些环境变量。在 tcsh 中，我可以使用别名 eval 组合。不能在 bash 中
我需要根据带参数的 perl 脚本的输出更改一些环境变量。在 tcsh 中，我可以使用别名命令来评估 perl 脚本的输出。 tcsh: alias setsdk 'eval `/localhome/
asp.net - Windows 身份验证适用于 IIS，但不适用于 Kestrel/Microsoft.AspNetCore.Authentication.Negotiate(不在 Chrome 中，有时在 Edge 中，始终在 IE 中)？
我使用 Windows 身份验证创建了一个新的 Blazor(服务器端)应用程序，并使用 IIS Express 运行它。它将显示一条消息“Hello Domain\User!”来自右上方的以下 Ra
java - java 中 Kotlin 中的等价物是什么？
这是我的方法 void login(Event event);我想知道 Kotlin 中应该如何最佳答案在 Kotlin 中通配符运算符是 * 。它指示编译器它是未知的，但一旦知道，就不会有其他类
express - 在 Jade 中，为什么有时我可以按原样使用变量而有时必须将它们包含在#{......} 中？
看下面的代码 for story in book if story.title.length < 140 - var story
c - C 中 strstr() 中 for 循环的错误使用
我正在尝试用 C 语言学习字符串处理。我写了一个程序，它存储了一些音乐轨道，并帮助用户检查他/她想到的歌曲是否存在于存储的轨道中。这是通过要求用户输入一串字符来完成的。然后程序使用 strstr()
c - * 在 sscanf 中，* 在 [] 中
我正在学习 sscanf 并遇到如下格式字符串: sscanf("%[^:]:%[^*=]%*[*=]%n",a,b,&c); 我理解 %[^:] 部分意味着扫描直到遇到 ':' 并将其分配给 a。:
python - 在 Python (2.7.3) 中，如果 str(x) 中的任何字符在 str(y) 中(或 str(y) 在 str(x) 中)，我如何编写一个函数来回答？
def char_check(x,y): if (str(x) in y or x.find(y) > -1) or (str(y) in x or y.find(x) > -1):
ansible - 在 Ansible 中，如何将一行移动到一个 block 中？
我有一种情况，我想将文本文件中的现有行包含到一个新 block 中。 line 1 line 2 line in block line 3 line 4 应该变成 line 1 line 2 line
Django 调试工具栏显示在根 URL 中，但不显示在应用程序 URL 中
我有一个新项目，我正在尝试设置 Django 调试工具栏。首先，我尝试了快速设置，它只涉及将 'debug_toolbar' 添加到我的已安装应用程序列表中。有了这个，当我转到我的根 URL 时，调试
r - 在 R 中，Matlab 中 @ 函数句柄的等价物是什么？
在 Matlab 中，如果我有一个函数 f，例如签名是 f(a,b,c)，我可以创建一个只有一个变量 b 的函数，它将使用固定的 a=a1 和 c=c1 调用 f: g = @(b) f(a1, b,
swiftui - SwiftUI 中 ScrollView 中 VStack 元素中的神秘间距或填充
我不明白为什么 ForEach 中的元素之间有多余的垂直间距在 VStack 里面在 ScrollView 里面使用 GeometryReader 时渲染自定义水平分隔线。 Scrol
cookies - 什么应该存储在 session 中，什么应该存储在 cookie 中？
我想知道，是否有关于何时使用 session 和 cookie 的指南或最佳实践？什么应该和什么不应该存储在其中？谢谢! 最佳答案这些文档很好地了解了 session cookie 的安全问题以及
python - Python 中 matplotlib 中 3d 直方图的奇怪行为
我在 scipy/numpy 中有一个 Nx3 矩阵，我想用它制作一个 3 维条形图，其中 X 轴和 Y 轴由矩阵的第一列和第二列的值、高度确定每个条形的是矩阵中的第三列，条形的数量由 N 确定。
c - c 中 sem_init(...) 中 value 参数的不同用法
假设我用两种不同的方式初始化信号量 sem_init(&randomsem,0,1) sem_init(&randomsem,0,0) 现在， sem_wait(&randomsem) 在这两种情况下
c - 实际值存储在 pstr 中，但是该值如何存储在数组 "WORD"中
我怀疑该值如何存储在“WORD”中，因为 PStr 包含实际输出。？既然Pstr中存储的是小写到大写的字母，那么在printf中如何将其给出为“WORD”。有人可以吗？解释一下？ #include
javascript - 数组索引选择像在 numpy 中，但在 javascript 中
我有一个 3x3 数组: var my_array = [[0,1,2], [3,4,5], [6,7,8]]; 并想获得它的第一个 2
javascript - 在 Javascript 中，如何检测浏览器窗口何时在 View 中？
我意识到您可以使用如下方式轻松检查焦点: var hasFocus = true; $(window).blur(function(){ hasFocus = false; }); $(win

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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

基础知识

查询