CODEWITHSUNDEEP
sqlarraysjsonpostgresqljsonb
Ryan Brockhoff
Ryan Brockhoff

Reputation: 706

JSONB to Array Data Type

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

Screenshot of Sessions Table

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.

Upvotes: 12

Views: 25245

Answers (2)

Ryan Brockhoff
Ryan Brockhoff

Reputation: 706

I was able to reach the answer by building off of your query! Thank you so much. The query that got the expected outcome was this: 

with exports as (
    select session_id, jsonb_array_elements_text(actions)::jsonb as doc from sessions
)
select session_id, jsonb_agg(doc) from exports
group by session_id;

I wasn't using the jsonb_agg() function once I got the elements. The difference for getting the exact format was just using jsonb_array_elements_text::jsonb

Upvotes: 2

S-Man
S-Man

Reputation: 23666

Does this help?

demo:db<>fiddle

SELECT
    jsonb_agg(elem) 
FROM
    sessions, jsonb_array_elements(actions) as elem
  1. jsonb_array_elements() expands the jsonb array into one row each jsonb element
  2. jsonb_agg() aggregates these jsonb elements into one big array.

Upvotes: 8

Related Questions