Hacker News Reader

Comment by jpdlla | Hacker News Reader

This looks great, but how does it compare to already existing options like django-jsonfield or jsonfield?

I looked at lots of alternatives. But I needed something that supported the actual underlying Postgresql json type. Psycopg2 converts those automatically into Python types. So we needed a JSON type that would accept: Python dicts, lists, strings, and JSON encoded objects, lists, and strings. None of them support that because they are all just storing the data as text in the backend.

I haven't looked into the postgres json field in depth yet, but it seems like you may know the answer. Is there any way of ensuring the structure / integrity of the data stored in it? Or is it currently considered to be totally freeform?

Postgresql validates the field. From the docs:

"the json data type has the advantage of checking that each stored value is a valid JSON value" - http://www.postgresql.org/docs/9.3/static/datatype-json.html

There are also a bunch of functions available to operate on a JSON field. Full info here: http://www.postgresql.org/docs/9.3/static/functions-json.htm.... What this means is that you can do pretty fast searches on the value of a key in a JSON object.

Thanks for the info - I'm thinking more along the lines of constraining the data within the valid json.

Just found this post which shows how you can do it.

    CREATE TABLE products (
        data JSON,
        CONSTRAINT validate_id CHECK ((data->>'id')::integer >= 1 AND (data->>'id') IS NOT NULL ),
        CONSTRAINT validate_name CHECK (length(data->>'name') > 0 AND (data->>'name') IS NOT NULL ),
        CONSTRAINT validate_description CHECK (length(data->>'description') > 0  AND (data->>'description') IS NOT NULL ),
        CONSTRAINT validate_price CHECK ((data->>'price')::decimal >= 0.0 AND (data->>'price') IS NOT NULL),
        CONSTRAINT validate_currency CHECK (data->>'currency' = 'dollars' AND (data->>'currency') IS NOT NULL),
        CONSTRAINT validate_in_stock CHECK ((data->>'in_stock')::integer >= 0 AND (data->>'in_stock') IS NOT NULL )
    }

http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-da...

Why would you ever want to do this? Surely at this point you are better with a table with id, name, description, price, currency, in_stock and an extra data field for anything else - given you require the other fields.

Depends on how much the data is going to be altered. Changing columns is not a free operation in data stores. They require you to rewrite all the rows in many cases.

This can be more dynamic short term.

Maybe - I need to test the performance, but for my application breaking things out into a table is a little slow. In all probability the constraints will be slower, but it's something to try.

At the moment I have all my data in mongo but over time it's fallen out of shape. There's a large chunk of it that just needs to be stores (json field) but it would be nice to constrain some of it.

Oh cool, that's very interesting! Thanks for sharing.

Thanks for the answer. Would it be possible to query against this field?

Not properly using Django's ORM. That's something I'll write when we actually start to need it. At the moment. It's more of a wholesale document store. Right now you would have to write a custom SQL query, using the Postgresql JSON functions, and the Model.objects.raw(...) interface of provided by Django.

The second you make it easy to query this, I'm dumping mongodb as my go to quick hack json store. Having one datastore > multiple datastores

I run django-jsonfield which has partial support for native Posgresql JSON type and more will be added as it's incorporated into Django: https://github.com/bradjasper/django-jsonfield/issues/55

Happy to accept pull requests for stuff like this if you're interested in contributing.

i use your lib all the time, works great! just wanted to say thanks

Great, I'll definitely do that. Maybe we should have a DM twitter chat on how to generalise it before I submit anything? I'm hanseldunlop there.

https://github.com/niwibe/djorm-ext-pgjson

There are similar libraries for hstore, arrays etc, They tend to follow the djorm-ext-* pattern.

https://github.com/niwibe/djorm-ext-core

https://github.com/niwibe/djorm-ext-pgarray

https://github.com/niwibe/djorm-ext-pgbytea

https://github.com/niwibe/djorm-ext-expressions

Reply on news.ycombinator.com