I currently have a table sessions with a column actions(JSONB).
I am able to properly store an array from my frontend to my database using array_to_jsonb(array value).
The structure of my array (array value) looks like this:
var arrayStructure = [
{name: 'email client', check: false},
{name: 'send contract', check: false}
]
Unlike a lot of the questions I've seen on SO about modifying JSONB or accessing certain keys, I am only interested in converting the entire JSONB data back to an array for the frontend.
My temporary fix is to map through the array and use JSON.parse() in Javascript to restructure each object in the array. I cannot use JSON.parse() on the entire array, as it throws an error.
I'm looking for a query to bring back the array format to the JSONB data type stored. I've seen JSONB_SET, LATERAL, AND JSONB_ARRAY_ELEMENTS_TEXT. But not in a way that worked to bring back a proper array.
Starts As: JSONB in actions column in table named sessions
Should Result In: A query that brings back all rows, but with the actions column (JSONB) converted back to an array for the frontend:
select session_id, session_name, someFunction or lateral here(actions) from sessions
I've tried queries like this:
SELECT
session_id,
actions::jsonb -> 'name' as name
FROM sessions;
And receive back null for name. I've tried ->> to access a deeper level, but that didn't work either.
This is half of the correct query result:
select session_id, jsonb_array_elements_text(actions)
from sessions
group by session_id;
Which results in this (only pay attention to results for session_id of 264): query result
Now I have objects in their own rows as:
{"name": "some task", "check": "false}
When what I want for the actions column is:
[ {name: "some task", check: false}, {name: "other task", check: true} ]
So I need to further parse the JSON and group by session_id. I'm just struggling to build a sub-query that does that.
Steps to Create Set Up:
create table fakeSessions (
session_id serial primary key,
name varchar(20),
list jsonb
)
insert into fakeSessions(name, list)
VALUES(
'running',
'["{"name":"inquired","check":false}", "{"name":"sent online guide","check":false}", "{"name":"booked!","check":false}"]'
)
insert into fakeSessions(name, list)
VALUES(
'snowboarding',
'["{"name":"rental","check":false}", "{"name":"booked ski passes","check":false}", "{"name":"survey","check":false}"]'
)
The closest query I've created:
with exports as (
select jsonb_array_elements_text(actions)::jsonb as doc from sessions
)
select array_agg(doc) from
exports, sessions
group by session_id;
Get the text values, and then apply an aggregate function to those returned rows. Just can't get the select array_agg(doc) to work as expected. Most likely because I need a different function in that place.