Mixing
How to filter jsonb array elements
0
I have a table with a jsonb column which contains an array of objects:
create table my_table(
id int primary key,
phones jsonb);
The objects consist of phone numbers and statuses.
insert into my_table values
(1,
'[
{
"phone": "929908338",
"status": "active"
},
{
"phone": "927007729",
"status": "inactive"
},
{
"phone": "927220419",
"status": "inactive"
},
{
"phone": "928200028",
"status": "inactive"
},
{
"phone": "927183322",
"status": "inactive"
},
{
"phone": "928424554",
"status": "active"
},
{
"phone": "927779383",
"status": "inactive"
}
]');
I want to get all phones with active status in a single row. Expected output:
["929908338","928424554"]
json postgresql aggregate-functions jsonb
edited Jan 2 at 13:16
klin
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
add a comment |
0
I have a table with a jsonb column which contains an array of objects:
create table my_table(
id int primary key,
phones jsonb);
The objects consist of phone numbers and statuses.
insert into my_table values
(1,
'[
{
"phone": "929908338",
"status": "active"
},
{
"phone": "927007729",
"status": "inactive"
},
{
"phone": "927220419",
"status": "inactive"
},
{
"phone": "928200028",
"status": "inactive"
},
{
"phone": "927183322",
"status": "inactive"
},
{
"phone": "928424554",
"status": "active"
},
{
"phone": "927779383",
"status": "inactive"
}
]');
I want to get all phones with active status in a single row. Expected output:
["929908338","928424554"]
json postgresql aggregate-functions jsonb
edited Jan 2 at 13:16
klin
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
add a comment |
0
0
0
I have a table with a jsonb column which contains an array of objects:
create table my_table(
id int primary key,
phones jsonb);
The objects consist of phone numbers and statuses.
insert into my_table values
(1,
'[
{
"phone": "929908338",
"status": "active"
},
{
"phone": "927007729",
"status": "inactive"
},
{
"phone": "927220419",
"status": "inactive"
},
{
"phone": "928200028",
"status": "inactive"
},
{
"phone": "927183322",
"status": "inactive"
},
{
"phone": "928424554",
"status": "active"
},
{
"phone": "927779383",
"status": "inactive"
}
]');
I want to get all phones with active status in a single row. Expected output:
["929908338","928424554"]
json postgresql aggregate-functions jsonb
edited Jan 2 at 13:16
klin
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
I have a table with a jsonb column which contains an array of objects:
create table my_table(
id int primary key,
phones jsonb);
The objects consist of phone numbers and statuses.
insert into my_table values
(1,
'[
{
"phone": "929908338",
"status": "active"
},
{
"phone": "927007729",
"status": "inactive"
},
{
"phone": "927220419",
"status": "inactive"
},
{
"phone": "928200028",
"status": "inactive"
},
{
"phone": "927183322",
"status": "inactive"
},
{
"phone": "928424554",
"status": "active"
},
{
"phone": "927779383",
"status": "inactive"
}
]');
I want to get all phones with active status in a single row. Expected output:
["929908338","928424554"]
json postgresql aggregate-functions jsonb
json postgresql aggregate-functions jsonb
edited Jan 2 at 13:16
klin
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
edited Jan 2 at 13:16
klin
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
edited Jan 2 at 13:16
klin
60.4k65686
edited Jan 2 at 13:16
klin
60.4k65686
edited Jan 2 at 13:16
klin
60.4k65686
60.4k65686
asked Jan 2 at 12:57
lalollalol
81
asked Jan 2 at 12:57
lalollalol
81
asked Jan 2 at 12:57
lalollalol
81
81
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
0
Use jsonb_array_elements() to unnest the jsonb array:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
id | elem
----+----------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "927007729", "status": "inactive"}
1 | {"phone": "927220419", "status": "inactive"}
1 | {"phone": "928200028", "status": "inactive"}
1 | {"phone": "927183322", "status": "inactive"}
1 | {"phone": "928424554", "status": "active"}
1 | {"phone": "927779383", "status": "inactive"}
(7 rows)
Add a condition to get elements you are looking for:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
id | elem
----+--------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "928424554", "status": "active"}
(2 rows)
Use the aggregate function jsonb_agg() to get the result in a single row:
select id, jsonb_agg(elem->'phone') as active_phones
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
group by id
id | active_phones
----+----------------------------
1 | ["929908338", "928424554"]
(1 row)
answered Jan 2 at 13:11
klinklin
60.4k65686
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
add a comment |
0
You can use a simple for loop to get this done
var obj = [{"phone": "929908338", "status": "active"}, {"phone": "927007729", "status": "inactive"}, {"phone": "927220419", "status": "inactive"}, {"phone": "928200028", "status": "inactive"}, {"phone": "927183322", "status": "inactive"}, {"phone": "928424554", "status": "active"}, {"phone": "927779383", "status": "inactive"}];
var tmpObj = ;
var i;
for (i = 0; i < obj.length; i++) {
if (obj[i]['status'] == 'active') {
tmpObj.push(obj[i]['phone'])
}
console.log(tmpObj);
}
This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]
There may be a more effective way of doing this but it is the simplest way I could think of
answered Jan 2 at 13:26
RajbirRajbir
47118
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
});
}
});
draft saved
draft discarded
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%2f54006818%2fhow-to-filter-jsonb-array-elements%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Use jsonb_array_elements() to unnest the jsonb array:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
id | elem
----+----------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "927007729", "status": "inactive"}
1 | {"phone": "927220419", "status": "inactive"}
1 | {"phone": "928200028", "status": "inactive"}
1 | {"phone": "927183322", "status": "inactive"}
1 | {"phone": "928424554", "status": "active"}
1 | {"phone": "927779383", "status": "inactive"}
(7 rows)
Add a condition to get elements you are looking for:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
id | elem
----+--------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "928424554", "status": "active"}
(2 rows)
Use the aggregate function jsonb_agg() to get the result in a single row:
select id, jsonb_agg(elem->'phone') as active_phones
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
group by id
id | active_phones
----+----------------------------
1 | ["929908338", "928424554"]
(1 row)
answered Jan 2 at 13:11
klinklin
60.4k65686
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
add a comment |
0
Use jsonb_array_elements() to unnest the jsonb array:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
id | elem
----+----------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "927007729", "status": "inactive"}
1 | {"phone": "927220419", "status": "inactive"}
1 | {"phone": "928200028", "status": "inactive"}
1 | {"phone": "927183322", "status": "inactive"}
1 | {"phone": "928424554", "status": "active"}
1 | {"phone": "927779383", "status": "inactive"}
(7 rows)
Add a condition to get elements you are looking for:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
id | elem
----+--------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "928424554", "status": "active"}
(2 rows)
Use the aggregate function jsonb_agg() to get the result in a single row:
select id, jsonb_agg(elem->'phone') as active_phones
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
group by id
id | active_phones
----+----------------------------
1 | ["929908338", "928424554"]
(1 row)
answered Jan 2 at 13:11
klinklin
60.4k65686
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
add a comment |
0
0
0
Use jsonb_array_elements() to unnest the jsonb array:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
id | elem
----+----------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "927007729", "status": "inactive"}
1 | {"phone": "927220419", "status": "inactive"}
1 | {"phone": "928200028", "status": "inactive"}
1 | {"phone": "927183322", "status": "inactive"}
1 | {"phone": "928424554", "status": "active"}
1 | {"phone": "927779383", "status": "inactive"}
(7 rows)
Add a condition to get elements you are looking for:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
id | elem
----+--------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "928424554", "status": "active"}
(2 rows)
Use the aggregate function jsonb_agg() to get the result in a single row:
select id, jsonb_agg(elem->'phone') as active_phones
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
group by id
id | active_phones
----+----------------------------
1 | ["929908338", "928424554"]
(1 row)
answered Jan 2 at 13:11
klinklin
60.4k65686
Use jsonb_array_elements() to unnest the jsonb array:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
id | elem
----+----------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "927007729", "status": "inactive"}
1 | {"phone": "927220419", "status": "inactive"}
1 | {"phone": "928200028", "status": "inactive"}
1 | {"phone": "927183322", "status": "inactive"}
1 | {"phone": "928424554", "status": "active"}
1 | {"phone": "927779383", "status": "inactive"}
(7 rows)
Add a condition to get elements you are looking for:
select id, elem
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
id | elem
----+--------------------------------------------
1 | {"phone": "929908338", "status": "active"}
1 | {"phone": "928424554", "status": "active"}
(2 rows)
Use the aggregate function jsonb_agg() to get the result in a single row:
select id, jsonb_agg(elem->'phone') as active_phones
from my_table
cross join jsonb_array_elements(phones) as elem
where elem->>'status' = 'active'
group by id
id | active_phones
----+----------------------------
1 | ["929908338", "928424554"]
(1 row)
answered Jan 2 at 13:11
klinklin
60.4k65686
answered Jan 2 at 13:11
klinklin
60.4k65686
answered Jan 2 at 13:11
klinklin
60.4k65686
answered Jan 2 at 13:11
klinklin
60.4k65686
60.4k65686
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
add a comment |
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
it solved every problems #) thanks!!
– lalol
Jan 2 at 13:54
add a comment |
0
You can use a simple for loop to get this done
var obj = [{"phone": "929908338", "status": "active"}, {"phone": "927007729", "status": "inactive"}, {"phone": "927220419", "status": "inactive"}, {"phone": "928200028", "status": "inactive"}, {"phone": "927183322", "status": "inactive"}, {"phone": "928424554", "status": "active"}, {"phone": "927779383", "status": "inactive"}];
var tmpObj = ;
var i;
for (i = 0; i < obj.length; i++) {
if (obj[i]['status'] == 'active') {
tmpObj.push(obj[i]['phone'])
}
console.log(tmpObj);
}
This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]
There may be a more effective way of doing this but it is the simplest way I could think of
answered Jan 2 at 13:26
RajbirRajbir
47118
add a comment |
0
You can use a simple for loop to get this done
var obj = [{"phone": "929908338", "status": "active"}, {"phone": "927007729", "status": "inactive"}, {"phone": "927220419", "status": "inactive"}, {"phone": "928200028", "status": "inactive"}, {"phone": "927183322", "status": "inactive"}, {"phone": "928424554", "status": "active"}, {"phone": "927779383", "status": "inactive"}];
var tmpObj = ;
var i;
for (i = 0; i < obj.length; i++) {
if (obj[i]['status'] == 'active') {
tmpObj.push(obj[i]['phone'])
}
console.log(tmpObj);
}
This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]
There may be a more effective way of doing this but it is the simplest way I could think of
answered Jan 2 at 13:26
RajbirRajbir
47118
add a comment |
0
0
0
You can use a simple for loop to get this done
var obj = [{"phone": "929908338", "status": "active"}, {"phone": "927007729", "status": "inactive"}, {"phone": "927220419", "status": "inactive"}, {"phone": "928200028", "status": "inactive"}, {"phone": "927183322", "status": "inactive"}, {"phone": "928424554", "status": "active"}, {"phone": "927779383", "status": "inactive"}];
var tmpObj = ;
var i;
for (i = 0; i < obj.length; i++) {
if (obj[i]['status'] == 'active') {
tmpObj.push(obj[i]['phone'])
}
console.log(tmpObj);
}
This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]
There may be a more effective way of doing this but it is the simplest way I could think of
answered Jan 2 at 13:26
RajbirRajbir
47118
You can use a simple for loop to get this done
var obj = [{"phone": "929908338", "status": "active"}, {"phone": "927007729", "status": "inactive"}, {"phone": "927220419", "status": "inactive"}, {"phone": "928200028", "status": "inactive"}, {"phone": "927183322", "status": "inactive"}, {"phone": "928424554", "status": "active"}, {"phone": "927779383", "status": "inactive"}];
var tmpObj = ;
var i;
for (i = 0; i < obj.length; i++) {
if (obj[i]['status'] == 'active') {
tmpObj.push(obj[i]['phone'])
}
console.log(tmpObj);
}
This method will loop through all the records and push the 'active' numbers to a tmp array. This will output ["929908338", "928424554"]
There may be a more effective way of doing this but it is the simplest way I could think of
answered Jan 2 at 13:26
RajbirRajbir
47118
answered Jan 2 at 13:26
RajbirRajbir
47118
answered Jan 2 at 13:26
RajbirRajbir
47118
answered Jan 2 at 13:26
RajbirRajbir
47118
47118
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f54006818%2fhow-to-filter-jsonb-array-elements%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
