arraysjsondatabasepostgresqlpostgresql-14

I need to create a view in postgres, which extracts the elements of a json column


This is my view in postgresql with json columns(labtestgroup and price)

CREATE OR REPLACE VIEW public.orders_with_prices
AS SELECT o.id,
    replace(''::text || o.labtestgroup, '"'::text, ''::text) AS labtestgroup,
    o.price,
   FROM ( SELECT orders.id,
            json_array_elements(orders."labTestGroups") AS labtestgroup,
            json_array_elements(orders.prices) AS price,
           FROM orders) o;

labtestgroups and prices are json arrays something like this ["12","213"]

In my table the records are divided, but I have records that have an empty array [ ], and those records do not appear in my table.

Actually view=

id labtestgroup prices
1 234 23
1 245 25
4 525 12

I need the empty records to appear, it doesn't matter the value can be [],0 or null

id Labtestgroup prices
1 234 23
1 245 25
2 [ ] [ ]
3 [ ] [ ]
4 525 12

Solution

  • json_array_elements function in Postgres won't produce rows if the input JSON array is empty or the JSON field is NULL, that's why you only see orders with json data. To avoid this use left lateral joins to the output of json_array_elements

    CREATE OR REPLACE VIEW PUBLIC.orders_with_prices AS
    
    SELECT
          o.id
        , COALESCE(replace(''::TEXT || l.labtestgroup, '"'::TEXT, ''::TEXT), '[]') AS labtestgroup
        , COALESCE(p.price, '[]') AS price
    FROM orders o
    LEFT JOIN LATERAL json_array_elements(o."labTestGroups") l(labtestgroup) ON TRUE
    LEFT JOIN LATERAL json_array_elements(o.prices) p(price) ON TRUE;
    

    Then coalesce is used to output [] if the json data is null. It may be easier to output 0 or NULL instead as then the columns will be more consistent (and you may want to cast either or both to numeric).