英文:
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;
答案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'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论