handling result if a field is null from database golang mysql

Question

type Users struct {
    ID    int    `json:"id"`
    Name  string `json:"name"`
    Age   string `json:"age"`
}

I have a mysql database in which some age values are nil so basically to make it dynamic I've been searching for the solution. "Age string json:-" to hide the field if it is returnig the value nil from mysql. I did two queries

   query1: select id,name,age from users where age is not null
   query2: select id,name from users where age is null

How can I make it a dynamic query to show the age if exsits else it doesn't don't show it?

There is no way to create dynamic struct with different json tags or hiding the value. — Himanshu
– Himanshu, Commented Apr 9, 2018 at 11:02
@RaymondNijland i was talking on the result that will be shown as a json value , the table have a row called age but it has some null values so i want to control the struct where the age is null it will just show the id and user else it will show all the fields — Medone
– Medone, Commented Apr 9, 2018 at 11:04
@Medone you'll have to convert the int to int64 before you use sql.NullInt64. And if you opt for the alternative you can use COALESCE(age, 0) as well. — mkopriva
– mkopriva, Commented Apr 9, 2018 at 11:37
@Medone use ,omitempty in the json tag if you want to avoid null properties in your json. — mkopriva
– mkopriva, Commented Apr 9, 2018 at 13:41

mkopriva · Accepted Answer · 2018-04-09 13:42:34Z

9

Don't do that. Instead, use a nil-able type for your Age field. *string or sql.NullString would be the most natural choices. Then just always select it, and the NULL value will be treated properly.

type Users struct {
    ID    int     `json:"id"`
    Name  string  `json:"name"`
    Age   *string `json:"age"`
}

Then always use:

SELECT id, name, age FROM users

And when Age is NULL in the database, it will be nil in Go, and when it's not NULL in the database, it will be a non-nil pointer to a string in Go.

edited Apr 9, 2018 at 13:42

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

answered Apr 9, 2018 at 11:04

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yash Goel · Accepted Answer · 2018-04-11 12:01:17Z

3

Use ifnull() in your query, so that null values can be fetched as ''(string's default value)

select id,name,ifnull(age, '') from users;

Then use omitempty in json tag of Age field.

type Users struct {
ID    int    `json:"id"`
Name  string `json:"name"`
Age   string `json:"age,omitempty"`
}

If default value is fetched in Age, then it will be ignored in json response.

answered Apr 11, 2018 at 12:01

Yash Goel

5501 gold badge6 silver badges16 bronze badges

Comments

Luka · Accepted Answer · 2023-05-08 14:49:01Z

0

You can use gonull (https://github.com/lomsa-dev/gonull):

type Users struct {
    ID    gonull.Nullable[int]    `json:"id"`
    Name  gonull.Nullable[string] `json:"name"`
    Age   gonull.Nullable[string] `json:"age,omitempty"`
}

answered May 8, 2023 at 14:49

Luka

1291 silver badge5 bronze badges

Collectives™ on Stack Overflow

handling result if a field is null from database golang mysql

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related