I have problems to scan json objects which include daterange types:
Let's assume these tables:
CERATE TABLE first_table (
id int PRIMARY KEY,
name text
);
CERATE TABLE second_table (
id int PRIMARY KEY,
title text,
period daterange NOT NULL,
a_id int NOT NULL REFERENCES first_table (id)
);
The structs are
type A struct {
ID int
Name *string
SomeBs []B
}
type B struct {
ID int
Title *string
Period pgtype.Range[pgtype.Date]
}
The query:
SELECT
a.id,
a.name,
bs.*
FROM first_table a
LEFT JOIN LATERAL (
SELECT
COALESCE(json_agg(json_build_object(
'id', id,
'title', title,
'period', period)), '[]')
FROM second_table b
WHERE a.id = b.a_id
) AS bs ON true
I get the error can't scan into dest[2]: json: cannot unmarshal string into Go struct field B.Period of type pgtype.Range[github.com/jackc/pgx/v5/pgtype.Date]
As writte here, scanning it without json_agg
and json_build_object
, it works.
If for testing purpose I changed the type B.Period to string, the query works and the value is something like [2020-02-01,2023-02-03)
. I tried to change the type to date_range.DateRange
or pgtype.Range[differentTypes]
, nothing worked.
How can I scan the values in date_range.DateRange
or pgtype.Range[pgtype.Date]
? I would prefer the latter.