That's a good article, if you want more - ping me. And that's exactly the idea I was referring to when I said I'd be willing to learn some new operators if it bought me a lot. And, since I tend to write like I know what I'm talking about no matter if I do or don't ;-) At the least, John DeSoi and Jim Crate on this list seem to *really* know what they're talking about with regards to Postgres.
Yeah, Postgres has several ways of dealing with JSON. You can use a plain JSON field if all you want to do is pipeline or store some JSON for some reason (pre-rendered values for pulls, logged API calls for review, etc.) JSONB is a lot different and takes advantage of the GIN index. From the sound of it, the GIN index is the real genius of the JSONB type. GIN has been in development for over a decade and helps provide Solr-like search features. (Finding words based on how near they are to other words, for example.) For space, JSON fields are smaller than comparable 4D object fields because they don't default to UTF16 and can be configured to use a much smaller collation. Assuming you can it your data into a smaller character encoding/set/whatever you call it. JSONB isn't space-optimized by default, but you can get an add-on to improve storage by a factor of roughly 6:1. But, really, the reason to use JSONB instead of JSON is for the searching. If you just need to store JSON, a JSON field is fine. (A text field is also fine, but a JSON field gives you database-level validation that the string *is* JSON.) Exciting stuff. I've read a ton about it and done some tests. Rob's plugin doesn't have an explicit command for transferring object fields, but you can stringify them and send them as text. The default rules for escaping text in Postgres are easy: -- Convert single quotes into two single quotes. -- Put single quotes around the whole block. If you have exotic searching requirements, Postgres has a ton of engine-level features...and a common add-on gives you a bunch of fuzzy matching tools. I was talking with John DeSoi the other day and he says that the fuzzy tools add-on includes "tri-grams". That's "n-grams" or "q-grams" with a length of 3. I've got a stack of research papers on why trigrams are so frickin amazing at fuzzy matching. I've tried (and failed) to implement them effectively in 4D a couple of times. WIth Postgres, you just add the fuzzy tools and away you go. ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: http://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
