从PostgreSQL的jsonb数据中选择内部json中的数据。

huangapple go评论63阅读模式
英文:

select data from internal json in jsonb postgresql

问题

如何从内部JSON中选择数据,例如 'select details.body.prop3 ...'?

如何从内部JSON中选择数据,例如 'select details.body.prop3 ...'?

英文:

I have the following table structure:

create table system_log
(
    id bigserial primary key,
    details jsonb
);

Data in 'details' column is json with inner json:

{
  "prop1": "value1",
  "prop2": "value2",
  "body": "{\"prop3\": \"value3\"}"
}

How to select data from inner json like 'select details.body.prop3 ...' ?

答案1

得分: 0

插入代码如下:

insert into system_log values (1, 
'{
"prop1":"value1",
"prop2":"value2",
"body":{"prop3":"value3"}
}');

查询代码如下:

select jsonb_path_query(details ,'$.body.prop3 ') 
from system_log sl; 

或者也可以使用条件查询:

select jsonb_path_query(details ,'$.body.prop3 ? (@ =="value3")') 
from system_log sl;

示例可在此处查看:链接
文档

英文:

Having

insert into system_log values (1, 
'{
"prop1":"value1",
"prop2":"value2",
"body":{"prop3":"value3"}
}');

You could query like this

select jsonb_path_query(details ,'$.body.prop3 ') 
from system_log sl; 

Or also use a condition

select jsonb_path_query(details ,'$.body.prop3 ? (@ =="value3")') 
from system_log sl;

Working sample here
and the docs

答案2

得分: 0

以下是已翻译的内容:

"The "body" stored as string instead of object json."

应为:

"将"body"存储为字符串而不是JSON对象。"

was:

{
  "prop1": "value1",
  "prop2": "value2",
  "body": "{\"prop3\": \"value3\"}"
}

应为:

{
  "prop1": "value1",
  "prop2": "value2",
  "body": {"prop3": "value3"}
}

This is why all other suggested answers returned "NULL" as result.
Thanks for @AdrianKlaver

英文:

The "body" stored as string instead of object json.

was:

{
  "prop1": "value1",
  "prop2": "value2",
  "body": "{\"prop3\": \"value3\"}"
}

should be

{
  "prop1": "value1",
  "prop2": "value2",
  "body": {"prop3": "value3"}
}

This is why all other suggested answers returned "NULL" as result.
Thanks for @AdrianKlaver

答案3

得分: 0

不应将JSON文档存储为其他JSON文档中的字符串。将 "body": "{\"prop3\": \"value3\"}" 更改为 "body": {"prop3": "value3"},然后您可以使用 details->'body'->>'prop3'details#>>'{body,prop3}' 正常访问该值。

但是,如果您确实需要使用嵌套格式,仍然可以获取内部文档的字符串,将其转换为 jsonb,然后访问该对象的属性:

(details ->> 'body')::jsonb ->> 'prop3'
英文:

You should not store JSON documents as strings in other JSON documents. Change "body": "{\"prop3\": \"value3\"}" to "body": {"prop3": "value3"}, and you can normally access the value using details->'body'->>'prop3' or details#>>'{body,prop3}'.

However, if you absolutely have to work with the nested format, you can still get the string of the inner document, convert it to jsonb, and then access properties of that object:

(details ->> 'body')::jsonb ->> 'prop3'

huangapple
  • 本文由 发表于 2023年7月10日 23:52:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/76655402.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定