Mixing
How to create index on json array in postgres?
I have a json field called 'elements' in my table demo which contains an array 'data' containing key value pairs. the 'data' array has the below structure. the data array may have multiple json entries.I am using postgres version 9.5
{
"data": [{
"ownr": "1",
"sigUsr": [2],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}]
}
In my query I have multiple filters based on obj(Eg : obj->>usrId, obj->>sigUsr etc) where obj corresponds to json_array_elements(demo.elements->'data').How do I create btree indices on filters like obj->>userId ,obj->>sigUsr? Please revert.
Regards
sur
postgresql postgresql-9.5
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
asked Jan 1 at 4:51
sursur
61
add a comment |
I have a json field called 'elements' in my table demo which contains an array 'data' containing key value pairs. the 'data' array has the below structure. the data array may have multiple json entries.I am using postgres version 9.5
{
"data": [{
"ownr": "1",
"sigUsr": [2],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}]
}
In my query I have multiple filters based on obj(Eg : obj->>usrId, obj->>sigUsr etc) where obj corresponds to json_array_elements(demo.elements->'data').How do I create btree indices on filters like obj->>userId ,obj->>sigUsr? Please revert.
Regards
sur
postgresql postgresql-9.5
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
asked Jan 1 at 4:51
sursur
61
add a comment |
I have a json field called 'elements' in my table demo which contains an array 'data' containing key value pairs. the 'data' array has the below structure. the data array may have multiple json entries.I am using postgres version 9.5
{
"data": [{
"ownr": "1",
"sigUsr": [2],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}]
}
In my query I have multiple filters based on obj(Eg : obj->>usrId, obj->>sigUsr etc) where obj corresponds to json_array_elements(demo.elements->'data').How do I create btree indices on filters like obj->>userId ,obj->>sigUsr? Please revert.
Regards
sur
postgresql postgresql-9.5
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
asked Jan 1 at 4:51
sursur
61
I have a json field called 'elements' in my table demo which contains an array 'data' containing key value pairs. the 'data' array has the below structure. the data array may have multiple json entries.I am using postgres version 9.5
{
"data": [{
"ownr": "1",
"sigUsr": [2],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}]
}
In my query I have multiple filters based on obj(Eg : obj->>usrId, obj->>sigUsr etc) where obj corresponds to json_array_elements(demo.elements->'data').How do I create btree indices on filters like obj->>userId ,obj->>sigUsr? Please revert.
Regards
sur
postgresql postgresql-9.5
postgresql postgresql-9.5
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
asked Jan 1 at 4:51
sursur
61
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
asked Jan 1 at 4:51
sursur
61
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
edited Jan 1 at 7:06
Kaushik Nayak
19.8k41332
19.8k41332
asked Jan 1 at 4:51
sursur
61
asked Jan 1 at 4:51
sursur
61
asked Jan 1 at 4:51
sursur
61
61
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
First, if your column datatype is JSON rather than JSONB, you can only create index
on the whole column demo.elements. Only JSONB columns can have indices on json keys.
You might consider to change your datatype if you specify JSON in this table.
Then, I modify your situation into a test case as follows.
create table demo(
elements jsonb
);
insert into demo values(
'{
"data": [
{
"ownr": "1",
"sigUsr": [
2
],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}
]
}'
);
And query you ask can be achieved in the following ways as I can imagine.
postgres=# -- First possible query
postgres=# select elements->'data'->0->'usrId', elements->'data'->0->'sigUsr' from demo;
?column? | ?column?
----------+----------
"1" | [2]
(1 row)
postgres=# -- Second possible query, with jsonb_array_elements()
postgres=# select obj->>'usrId', obj->>'sigUsr' from demo d, jsonb_array_elements(d.elements->'data') as obj;
?column? | ?column?
----------+----------
1 | [2]
(1 row)
postgres=#
I can only create index with the first one, which is a restrictive use case. you need to write specific array entry in your index (in this case is the 0th element).
postgres=# create index ON demo ((elements->'data'->0->'usrId'));
CREATE INDEX
postgres=#
I can not create index for the second approach since jsonb_array_elements() returns setof jsonb type.
postgres=# create index ON demo ((jsonb_array_elements(elements->'data')->>'usrId'));
ERROR: set-returning functions are not allowed in index expressions
LINE 1: create index ON demo ((jsonb_array_elements(elements->'data'...
^
postgres=#
I think you should store sub-json entries in individual rows rather than store them into a json array.
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993075%2fhow-to-create-index-on-json-array-in-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
First, if your column datatype is JSON rather than JSONB, you can only create index
on the whole column demo.elements. Only JSONB columns can have indices on json keys.
You might consider to change your datatype if you specify JSON in this table.
Then, I modify your situation into a test case as follows.
create table demo(
elements jsonb
);
insert into demo values(
'{
"data": [
{
"ownr": "1",
"sigUsr": [
2
],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}
]
}'
);
And query you ask can be achieved in the following ways as I can imagine.
postgres=# -- First possible query
postgres=# select elements->'data'->0->'usrId', elements->'data'->0->'sigUsr' from demo;
?column? | ?column?
----------+----------
"1" | [2]
(1 row)
postgres=# -- Second possible query, with jsonb_array_elements()
postgres=# select obj->>'usrId', obj->>'sigUsr' from demo d, jsonb_array_elements(d.elements->'data') as obj;
?column? | ?column?
----------+----------
1 | [2]
(1 row)
postgres=#
I can only create index with the first one, which is a restrictive use case. you need to write specific array entry in your index (in this case is the 0th element).
postgres=# create index ON demo ((elements->'data'->0->'usrId'));
CREATE INDEX
postgres=#
I can not create index for the second approach since jsonb_array_elements() returns setof jsonb type.
postgres=# create index ON demo ((jsonb_array_elements(elements->'data')->>'usrId'));
ERROR: set-returning functions are not allowed in index expressions
LINE 1: create index ON demo ((jsonb_array_elements(elements->'data'...
^
postgres=#
I think you should store sub-json entries in individual rows rather than store them into a json array.
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
add a comment |
First, if your column datatype is JSON rather than JSONB, you can only create index
on the whole column demo.elements. Only JSONB columns can have indices on json keys.
You might consider to change your datatype if you specify JSON in this table.
Then, I modify your situation into a test case as follows.
create table demo(
elements jsonb
);
insert into demo values(
'{
"data": [
{
"ownr": "1",
"sigUsr": [
2
],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}
]
}'
);
And query you ask can be achieved in the following ways as I can imagine.
postgres=# -- First possible query
postgres=# select elements->'data'->0->'usrId', elements->'data'->0->'sigUsr' from demo;
?column? | ?column?
----------+----------
"1" | [2]
(1 row)
postgres=# -- Second possible query, with jsonb_array_elements()
postgres=# select obj->>'usrId', obj->>'sigUsr' from demo d, jsonb_array_elements(d.elements->'data') as obj;
?column? | ?column?
----------+----------
1 | [2]
(1 row)
postgres=#
I can only create index with the first one, which is a restrictive use case. you need to write specific array entry in your index (in this case is the 0th element).
postgres=# create index ON demo ((elements->'data'->0->'usrId'));
CREATE INDEX
postgres=#
I can not create index for the second approach since jsonb_array_elements() returns setof jsonb type.
postgres=# create index ON demo ((jsonb_array_elements(elements->'data')->>'usrId'));
ERROR: set-returning functions are not allowed in index expressions
LINE 1: create index ON demo ((jsonb_array_elements(elements->'data'...
^
postgres=#
I think you should store sub-json entries in individual rows rather than store them into a json array.
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
add a comment |
First, if your column datatype is JSON rather than JSONB, you can only create index
on the whole column demo.elements. Only JSONB columns can have indices on json keys.
You might consider to change your datatype if you specify JSON in this table.
Then, I modify your situation into a test case as follows.
create table demo(
elements jsonb
);
insert into demo values(
'{
"data": [
{
"ownr": "1",
"sigUsr": [
2
],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}
]
}'
);
And query you ask can be achieved in the following ways as I can imagine.
postgres=# -- First possible query
postgres=# select elements->'data'->0->'usrId', elements->'data'->0->'sigUsr' from demo;
?column? | ?column?
----------+----------
"1" | [2]
(1 row)
postgres=# -- Second possible query, with jsonb_array_elements()
postgres=# select obj->>'usrId', obj->>'sigUsr' from demo d, jsonb_array_elements(d.elements->'data') as obj;
?column? | ?column?
----------+----------
1 | [2]
(1 row)
postgres=#
I can only create index with the first one, which is a restrictive use case. you need to write specific array entry in your index (in this case is the 0th element).
postgres=# create index ON demo ((elements->'data'->0->'usrId'));
CREATE INDEX
postgres=#
I can not create index for the second approach since jsonb_array_elements() returns setof jsonb type.
postgres=# create index ON demo ((jsonb_array_elements(elements->'data')->>'usrId'));
ERROR: set-returning functions are not allowed in index expressions
LINE 1: create index ON demo ((jsonb_array_elements(elements->'data'...
^
postgres=#
I think you should store sub-json entries in individual rows rather than store them into a json array.
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
First, if your column datatype is JSON rather than JSONB, you can only create index
on the whole column demo.elements. Only JSONB columns can have indices on json keys.
You might consider to change your datatype if you specify JSON in this table.
Then, I modify your situation into a test case as follows.
create table demo(
elements jsonb
);
insert into demo values(
'{
"data": [
{
"ownr": "1",
"sigUsr": [
2
],
"sigStat": "APPR",
"modifiedOn": 1494229698039,
"isDel": "false",
"parentId": "nil",
"disName": "exmp.json",
"uniqueId": "d88cb52",
"usrType": "owner",
"usrId": "1",
"createdOn": 1494229698039,
"obType": "file"
}
]
}'
);
And query you ask can be achieved in the following ways as I can imagine.
postgres=# -- First possible query
postgres=# select elements->'data'->0->'usrId', elements->'data'->0->'sigUsr' from demo;
?column? | ?column?
----------+----------
"1" | [2]
(1 row)
postgres=# -- Second possible query, with jsonb_array_elements()
postgres=# select obj->>'usrId', obj->>'sigUsr' from demo d, jsonb_array_elements(d.elements->'data') as obj;
?column? | ?column?
----------+----------
1 | [2]
(1 row)
postgres=#
I can only create index with the first one, which is a restrictive use case. you need to write specific array entry in your index (in this case is the 0th element).
postgres=# create index ON demo ((elements->'data'->0->'usrId'));
CREATE INDEX
postgres=#
I can not create index for the second approach since jsonb_array_elements() returns setof jsonb type.
postgres=# create index ON demo ((jsonb_array_elements(elements->'data')->>'usrId'));
ERROR: set-returning functions are not allowed in index expressions
LINE 1: create index ON demo ((jsonb_array_elements(elements->'data'...
^
postgres=#
I think you should store sub-json entries in individual rows rather than store them into a json array.
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
answered Jan 1 at 8:04
C.C. HsuC.C. Hsu
511
511
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993075%2fhow-to-create-index-on-json-array-in-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
