Postgresql:JSON Array to Rows Using Lateral Join

PostgreSql : Json Array to Rows using Lateral Join

Seems you need WITH ORDINALITY along with LEFT JOIN LATERALs to match the corresponding elements of the arrays due to the order in the arrays, respectively :

SELECT q.elm AS quantities, p.elm AS prices, 
       AVG(p.elm::float/q.elm::float) AS ratio
  FROM my_table t0
  LEFT JOIN LATERAL jsonb_array_elements(details -> 'quantities') 
    WITH ORDINALITY AS q(elm, i) ON TRUE
  LEFT JOIN LATERAL jsonb_array_elements(details -> 'prices') 
    WITH ORDINALITY AS p(elm, i) ON q.i = p.i
  LEFT JOIN LATERAL jsonb_array_elements(details -> 'dates') 
    WITH ORDINALITY AS d(elm, i) ON d.i = q.i
 WHERE t0.details ->> 'city' = 'London'   
 GROUP BY q.elm, p.elm;

Demo

Postgres join on json array values

I suggest to use an ARRAY constructor in a LATERAL subquery:

SELECT p.id, j.users
FROM   post p
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT u.name
      FROM   jsonb_array_elements_text(p.attributes->'user_ids') WITH ORDINALITY j(user_id, ord)
      JOIN   users u ON u.id = j.user_id::int
      ORDER  BY j.ord
      ) AS users
   ) j
;

db<>fiddle here

Note that null values in the array are ignored.

The CROSS JOIN never eliminates rows in this case because the ARRAY constructor always returns a row, even when the JSON array is empty or NULL.

Related:

• How to show all dates from a certain date range in horizontal row?
• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
• PostgreSQL unnest() with element number
• Why is array_agg() slower than the non-aggregate ARRAY() constructor?
• How to turn JSON array into Postgres array?

Can PostgreSQL JOIN on jsonb array objects?

Yes it is possible to meet this requirement with Postgres. Here is a solution for 9.6 or higher.

SELECT o.id, JSON_AGG(
    JSON_BUILD_OBJECT(
        'id'    , u.id, 
        'name'  , u.name, 
        'email' , u.email, 
        'role'  , e.usr->'role'
    )
)
FROM organisations o
CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(o.data) AS e(usr)
INNER JOIN users u ON (e.usr->'id')::text::int = u.id
GROUP BY o.id

See this db fiddle.

Explanation :

• the JSONB_ARRAY_ELEMENTS function splits the organisation json array into rows (one per user) ; it is usually used in combination with JOIN LATERAL

• to join the users table, we access the content of the id field using the -> operator

• for each user, the JSONB_BUILD_OBJECT is used to create a new object, by passing a list of values/keys pairs ; most values comes from the users table, excepted the role, that is taken from the organisation json element

• the query aggregates by organisation id, using JSONB_AGG to generate a json array by combining above objects

For more information, you may also have a look at Postgres JSON Functions documentation.

How to turn a json array into rows in postgres

I post the answer originally written by pozs in the comment section.

unnest() is for PostgreSQL's array types.

Instead one of the following function can be used:

• json_array_elements(json) (9.3+)
• jsonb_array_elements(jsonb) (9.4+)
• json[b]_array_elements_text(json[b]) (9.4+)

Example:

select * from json_array_elements('[1,true, [2,false]]')

output value

 -------------
 | 1         |
 -------------
 | true      |
 -------------
 | [2,false] |
 -------------

Here where the documentation for v9.4 can be found.

Join Postgresql tables on json array columns

Actually, with the data type jsonb in Postgres 9.4 or later, this becomes dead simple. Your query would just work (ugly naming convention, code and duplicate names in the output aside).

CREATE TEMP TABLE table_a(a_id int, a int, b jsonb);
INSERT INTO table_a VALUES
  (1, 36464, '["874746", "474657"]')
, (2, 36465, '["874748"]')
, (3, 36466, '["874736", "474654"]');

CREATE TEMP TABLE table_b(b_id int, name text, b jsonb);
INSERT INTO table_b VALUES
  (1, 'john' , '["8740246", "2474657"]')
, (2, 'mary' , '["874748","874736"]')
, (3, 'clara', '["874736", "474654"]');

Query:

SELECT a_id, a, b.*
FROM   table_a a
JOIN   table_b b USING (b);  -- match on the whole jsonb column

That you even ask indicates you are using the data type json, for which no equality operator exists:

• How to query a json column for empty objects?

You just didn't mention the most important details.

The obvious solution is to switch to jsonb.

---

Answer to your comment

is it possible to flatten out b into new rows rather than an array?

Use jsonb_array_elements(jsonb) or jsonb_array_elements_text(jsonb) in a LATERAL join:

SELECT a_id, a, b.b_id, b.name, b_array_element
FROM   table_a a
JOIN   table_b b USING (b)
     , jsonb_array_elements_text(b) b_array_element

This returns only rows matching on the whole array. About LATERAL:

• What is the difference between LATERAL and a subquery in PostgreSQL?

If you want to match on array elements instead, unnest your arrays before you join.

The whole setup seems to be in dire need of normalization.

Group Json array data with multiple fields using CROSS JOIN LATERAL on PostgreSQL

You've not explained what "latest email" means, but I presume you want the record with the highest sub->id

You may use DISTINCT ON to get a highest in a group.

SELECT DISTINCT ON (jae.e->>'name') --for every name
    jae.e->>'name'  as name, 
    jae.e->>'email' as email
       FROM survey_results sr
         CROSS JOIN LATERAL jsonb_array_elements(sr.data_field)  jae (e)
       ORDER BY jae.e->>'name', jae.e->'sub'->>'id' desc 
                                         --^ return only the row with the highest id

DEMO

How to turn a json array into rows in postgresql

json_to_record and json_to_recordset from the JSON processing functions do exactly this. In your case:

SELECT cols.*
FROM json_to_recordset(yourJsonValue -> 'rows') AS rows(columns JSON),
     json_to_record(columns) AS cols(
       "Nombre" TEXT,
       "rutaEsquema" TEXT,
       "TipoDeComponente" TEXT,
       "detalleDelComponente" TEXT)

online demo

how to join on the values returned from Json_array_elements in postgresql?

Put the function call into the FROM clause and de-reference the actual field of the JSON object later.

select o.object_id, so.*
from "Order" o
  cross join lateral jsonb_array_elements(sub_orders) j(suborder)
  join sub_order so on so.object_id = j.suborder ->> 'objectId' 

Online example: http://rextester.com/GQBF88668

Should I use lateral when querying JSON arrays on postgresql?

This is a matter of preference.

Many users of Postgres have no problem using set-returning functions in the select clause. And obviously it is acceptable syntax. These are SQL extensions, so there is no "right" way as specified by the standard.

That said, I have a strong preference for putting set-returning functions in the from clause and using a lateral join. I want the from clause to answer the question: "what is the space of rows that are going into this query?". I want the "select" clause to answer: "what columns and expressions are being returned".

In addition, other databases tend to require that set-returning functions be in the from clause.

So, I find a lateral join to be more in the spirit of SQL. But both methods are correct.

---

Related Topics