Faster way to cycle through and combine opened JSON with Postgresql

Question

I have a DB collection with multiple JSON elements. I am trying to open each element to gather the info at the end point and then combine all the end point information.

Currently my plan is to use this:

Create Table P1S1C1 as
        select title,
        path,
        user,
        userP,
        active,
        created,
        updated,
        pro::json#\>\>'{0,s,0,c,0,signup}' as "signup",
        pro::json#\>\>'{0,s,0,c,0,finish}' as "finish",
        pro::json#\>\>'{0,s,0,c,0,cost}' as "cost",
        pro::json#\>\>'{0,s,0,c,0,status}' as "status",
        pro::json#\>\>'{0,s,0,c,0,startat}' as "startat"
  from db_st

However, each 0 in the query has multiple elements to open (first one (p) is 0,1/second one (s) is 0-44/third one (c) is 0-16). Example of the second query to open the next element in the first element:

 Create Table P1S2C1 as
             select title,
             path,
             user,
             userP,
             active,
             created,
             updated,
             pro::json#\>\>'{0,s,1,c,0,signup}' as "signup",
             pro::json#\>\>'{0,s,1,c,0,finish}' as "finish",
             pro::json#\>\>'{0,s,1,c,0,cost}' as "cost",
             pro::json#\>\>'{0,s,1,c,0,status}' as "status",
             pro::json#\>\>'{0,s,1,c,0,startat}' as "startat"
   from db_st

The only way I can think of is too do a table for each of these and join them but that would be 1408 tables!!

There has to be a better way. How can I do this faster?

It's not clear what you mean by "opening an element". Can you show us the json value stored in the pro column, and the desired result? — Bergi
– Bergi, Commented Apr 9, 2023 at 19:13
Do you really want to create a new table with the same values that are already stored in db_st? Sounds like you rather want a VIEW. — Bergi
– Bergi, Commented Apr 9, 2023 at 19:15
@Bergi this data is in MongoDB and I am viewing it in Studio 3T. The pro column is a JSON array and says [2 elements]. When you open (click on) the pro column it turns into 2 columns, 0 and 1, and each a JSON object with { 2 fields }. When you open the fields you get one column with a list of names and a second with a JSON array with up to 44 elements. When you open that you get the elements as JSON objects again with a final JSON array column, opening that final array has all the data I need and I need it for every object and array path. — Alane
– Alane, Commented Apr 10, 2023 at 18:11
@Bergi I need to have all that end point data, it can be a view if it is a single query. I am putting the data into Tableau so I can make visuals and I am able to run a custom SQL to transform the data how I need it. — Alane
– Alane, Commented Apr 10, 2023 at 18:14
You're looking for json_array_elements then (possibly nested) — Bergi
– Bergi, Commented Apr 10, 2023 at 18:14

Bergi · Accepted Answer · 2023-04-10 19:34:21Z

1

You seem to be looking for

select
  title,
  path,
  user,
  userP,
  active,
  created,
  updated,
  psc->>'signup' as signup,
  psc->>'finish' as finish,
  psc->>'cost' as cost,
  psc->>'status' as status,
  psc->>'startat' as startat
from
  db_st,
  lateral json_array_elements(pro) AS p,
  lateral json_array_elements(p->'s') AS ps,
  lateral json_array_elements(ps->'c') AS psc

edited Apr 10, 2023 at 19:34

answered Apr 10, 2023 at 18:43

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alane Over a year ago

Amazing!!! Thank you sooo much!!! Worked like a charm. I was trying to figure out how to use the json_array_elements function and from what I saw you needed to create a table. I am newer to the data world so I really appreciate your help.

Bergi Over a year ago

No, you don't need to create new tables, you just need to run the query

Collectives™ on Stack Overflow

Faster way to cycle through and combine opened JSON with Postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related