如何从存储在 oracle 数据库的一个单元格中的 KeyValuePair 数据中获取基于键的值-解网

问：

假设我有一张这样的桌子

id name             address                     Doc
1  {1:mark,2:john}  {1:Home,2:Work,3:Club}      {NI:299,Pass:A159}
2  {1:Max,2:Mo}     {1:Home}                    {NI:300011}

我想要的是编写一个 qurey 以根据键从单元格中选择一个值例如

i want the value which has key = 2 in column named(Name) where id=1

所以它应该返回

john

等等。那么，如何在不使用子字符串的情况下做这样的事情呢？

sql json oracle 键值

CREATE TABLE mytable (
  id int,
  name  VARCHAR2 (200) ,          
  address VARCHAR2 (200),                    
  Doc VARCHAR2 (200),
  CONSTRAINT ensure_json CHECK (name IS JSON)
);

insert into mytable 
select 1,  '{"1":"mark","2":"john"}', '{"1":"Home","2":"Work","3":"Club"}', '{"NI":299,"Pass":"A159"}' from dual union all
select 2,  '{"1":"Max","2":"Mo"}', '{"1":"Home"}', '{"NI":300011}' from dual

查询可以是：

select t.name."2"
from mytable t
where id = 1

演示在这里

如果无效的 JSON 中只有键值对值，则：

with cte as (
  select REGEXP_REPLACE(name,
                 '([a-zA-Z0-9-]+):([a-zA-Z0-9-]+)',
                 '"\1":"\2"') as name
  from mytable t
  where id = 1
)
select json_value(name, '$."2"')
from cte

演示在这里

1赞 Alex Poole 10/19/2023 #2

如果你的数据如图所示，不是JSON（或引用的），并且性能不是主要关注点（如果是，则返回到，如@MT0所示），那么你可以使用regexp_substr（）来查找匹配项，并嵌入所需的键值的模式：substr()

select regexp_substr(name, '({|,)' || 2 || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = 1

结果
John

您的脚本需要在第一行中提供第一个，在第三行中提供;因此，更通用地使用 bind 变量：21

select regexp_substr(name, '({|,)' || :p_key || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = :p_id

模式是查找或，后跟键值，后跟冒号;并将所有内容捕获到另一个或.其他参数用于告诉它返回第二个捕获组（该行中的第二个）。第一组是的 'or' ，你想要第二组，它是该对的值。{,:,}2({|,)(.*?)

通过改变键值，您可以获得不同的结果;使用相同的列和：1

select regexp_substr(name, '({|,)' || 1 || ':(.*?)(,|})', 1, 1, null, 2) as result
from your_table
where id = 1

结果
马克

您可以对其他键/值列使用相同的方法。

小提琴

当然，最好是以关系方式存储数据，而不是作为字符串列中的键/值对。

1赞 MT0 10/19/2023 #3

您的列不是有效的 JSON，因此您不能使用 JSON 函数。name

您可以使用正则表达式，但它们比简单的字符串函数慢。所以，即使你说你不想使用，你也应该使用：SUBSTRSUBSTR

SELECT id,
       CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name2
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ',2:', 1, 1) + 3
         ) AS epos
  FROM   table_name
)

其中，对于示例数据：

CREATE TABLE table_name (id, name) AS
SELECT 1, '{1:mark,2:john}' FROM DUAL UNION ALL
SELECT 2, '{1:Max,2:Mo}'    FROM DUAL;

输出：

编号	名称2
1	John
2	莫

如果您只想要何时添加该过滤器，并且如果您想制作索引，则要匹配一个更动态的参数，然后：id = 1

SELECT CASE
       WHEN spos > 0 AND epos > 0
       THEN SUBSTR(name, spos + 3, epos - spos - 3)
       END AS name
FROM   (
  SELECT id,
         name,
         INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1) AS spos,
         INSTR(
           TRANSLATE(name, '{}', ',,'),
           ',',
           INSTR(TRANSLATE(name, '{}', ',,'), ','||index_to_match||':', 1, 1) + 3
         ) AS epos
  FROM   table_name
         CROSS JOIN (SELECT 2 AS index_to_match FROM DUAL)
  WHERE  id = 1
)

或者使用参数化查询和 bind 参数（而不是子查询）。

小提琴

上一个：相同的数组，但通过迭代或使用键对 foreach 输出不同

下一个：我被困在调用字典中的键值中，该键位于列表内，但具有随机变量。其输出与列表中的输出不同

如何从存储在 oracle 数据库的一个单元格中的 KeyValuePair 数据中获取基于键的值

How to get a value based on a key from KeyValuePair data stored in one cell in oracle database

评论