Postgres JSON `->>` operator generates `string` not `*string`

[danthegoodman1]

Version

1.27.0

What happened?

-- name: ListJobs :many
-- Returns jobs with reduced data, to reduce payload size
select id,
       (data ->> 'PhoneNumber')::text as phone_number,
       (data ->> 'ContactName')::text as contact_name,
       (data ->> 'State')::text       as state
from jobs

In the JSON contact_name output is not always in the JSON, can fails to make a nullable string for the ListJobsRow type.

ContactName in the ListJobsRow will only ever be string or interface{}

COALESCE((data ->> 'ContactName')::text, NULL) as contact_name, makes it an interface{}

CASE WHEN (data ->> 'ContactName') IS NULL THEN NULL ELSE (data ->> 'ContactName')::text END as contact_name, returns a string

Considering the ->> operator can return a NULL if the property doesn't exist in the JSON, it should really be a *string or sql.NullString instead of string.

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

https://play.sqlc.dev/p/e5583c55e36fe049212dd7abbe79d8b560444d58675560b18375ffcb31ef8cd6

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[will-x86]

+1 to this, working on a project that requires optional values such as:

-- name: GetLatestSensorReadings :many
-- Gets the latest 10 sensor readings for a specific device 
SELECT 
    timestamp,
    (data->'temperature'->>'value') as temperature_value,
    data->'temperature'->>'unit' as temperature_unit,
    (data->'humidity'->>'value') as humidity_value,
    data->'humidity'->>'unit' as humidity_unit,
    (data->'illumination'->>'value') as illumination_value,
    data->'illumination'->>'unit' as illumination_unit,
    (data->'soundLevel'->>'value') as sound_level_value,
    data->'soundLevel'->>'unit' as sound_level_unit,
    (data->'supplyVoltage'->>'value') as supply_voltage_value,
    data->'supplyVoltage'->>'unit' as supply_voltage_unit,
    (data->>'motionDetected') as motion_detected
FROM sensor_readings
WHERE device_id = $1
ORDER BY timestamp DESC
LIMIT 10;

And I interfaces, and if I add ::text I end up with "cannot convert NULL to string"

Tried with: emit_pointers_for_null_types:true and false