如何使用JSON_TABLE从Oracle JSON列获取键值作为结果集 [英] How to get Key Value as resultset from Oracle JSON column using JSON_TABLE

问题描述

我在Google上搜索了很多，似乎无法找到针对我的简单用例的简单解决方案. 我在Oracle 12C数据库中有一个json列(当然，实际上是具有json约束的varchar)，在该列中，我存储了这样的Map表示形式

I have googled aplenty, and can't seem to find a simple solution to my simple use case. I have a json column in an Oracle 12C database (actually a varchar with json constraint of course), and in that column I store a representation of a Map like this

{
"a":9.0847,
"b":859.947
}

在plsql中，我想返回一个看起来像这样的结果集

In plsql I would like to return a result set of that looks like this

key val
a   9.0847
b   859.947

我在下面修补了看似无限的变体，所有示例对于我的用例而言都是太虚构了.

I have tinkered with seemingly infinite variations of this below, and all the examples are too contrived for my use case.

select b.* from mytable a,json_table(myJsonCol,'$'
columns ( value varchar2(500) path '$.myjsonkey')) b

，但这仅返回值列表，没有相应的键. json数据始终为字符串双键值.

but this only returns a list of values, without the corresponding keys. The json data is ALWAYS string-double key vals.

谢谢

编辑 要添加更多上下文，我现在在postgres中使用json_each进行此操作，并且我正在Oracle中寻找类似的方法.

EDIT To add a bit more context, i use json_each in postgres to do this now, and i'm looking for a similar method in Oracle.

推荐答案

您的JSON值是一个元组，因此您可以使用UNPIVOT将其转换为键/值对表:

Your JSON value is a single tuple, so you could use UNPIVOT to turn it into a table of key/value pairs:

with mydata as (
  select '{
  "a":9.0847,
  "b":859.947
  }' myjsoncol
  from dual
), q as (
  select json_value(mydata.myjsoncol, '$.a') ca
        ,json_value(mydata.myjsoncol, '$.b') cb
  from mydata
) select * from q
unpivot (val for key in (ca as 'a', cb as 'b'));

KEY VAL
=== =======
a   9.0847
b   859.947

实时SQL: https://livesql.oracle.com/apex/livesql/s/d31n9re90y6cpghi4i3m9hfoh

这篇关于如何使用JSON_TABLE从Oracle JSON列获取键值作为结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！