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

Question

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

Answer 1

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).