How to Work with SQL Null Values and JSON in a Good Way

How can I work with SQL NULL values and JSON in a good way?

Types like sql.NullInt64 do not implement any special handling for JSON marshaling or unmarshaling, so the default rules apply. Since the type is a struct, it gets marshalled as an object with its fields as attributes.

One way to work around this is to create your own type that implements the json.Marshaller / json.Unmarshaler interfaces. By embedding the sql.NullInt64 type, we get the SQL methods for free. Something like this:

type JsonNullInt64 struct {
    sql.NullInt64
}

func (v JsonNullInt64) MarshalJSON() ([]byte, error) {
    if v.Valid {
        return json.Marshal(v.Int64)
    } else {
        return json.Marshal(nil)
    }
}

func (v *JsonNullInt64) UnmarshalJSON(data []byte) error {
    // Unmarshalling into a pointer will let us detect null
    var x *int64
    if err := json.Unmarshal(data, &x); err != nil {
        return err
    }
    if x != nil {
        v.Valid = true
        v.Int64 = *x
    } else {
        v.Valid = false
    }
    return nil
}

If you use this type in place of sql.NullInt64, it should be encoded as you expect.

You can test this example here: http://play.golang.org/p/zFESxLcd-c

Create an array/json from columns excluding NULL values in Bigquery

Any idea why would this happen?

when you do to_json_string - all nulls becomes strings 'null's

Use below instead

select array_agg(a) as phones
from material, 
unnest(json_extract_array(to_json_string([phone_1,phone_2,phone_3,phone_4,phone_5,phone_6]))) a
where a != 'null'         

with output

How can JSON data with null value be converted to a dictionary

You should use the built-in json module, which was designed explicitly for this task:

>>> import json
>>> data = '''
... {
...   "abc": null,
...   "def": 9
... }
... '''
>>> json.loads(data)
{'def': 9, 'abc': None}
>>> type(json.loads(data))
<class 'dict'>
>>>

By the way, you should use this method even if your JSON data contains no null values. While it may work (sometimes), ast.literal_eval was designed to evaluate Python code that is represented as a string. It is simply the wrong tool to work with JSON data.

How to deal with mapping intentional NULL values passed through JSON?

The comment provided by Andy is correct, I also don't know the difference between the two you mentioned in your question. I think the value of OptionalProperty3 is null is same with OptionalProperty3 is not-set.

If you still want to distinguish between them, here I can provide a workaround for your reference. Replace the null in JsonExample1 with "null" string, please refer to my code below:

---

Related Topics