On 12/2/15 10:38 PM, Kaare Rasmussen wrote:
> On 2015-12-03 01:04, Jim Nasby wrote:
>> We have a client that has a similar (though also a bit different)
>> need. Specifically, they get an XML document that has element
>> attributes that tell you what data type the element should contain. We
>> convert the XML to JSON (easy thanks to plpython), which produces a
>> bunch of nested JSON objects (typed as specifically as possible in
>> JSON). The XML attributes get turned into items in an object. So
>
> OK, AFAIUI, you added the schema to each row. I think that I have fewer
> variations, so perhaps the information would live better outside, but
> that's a detail. Turning them into tables and views is a good way to
> represent the indexable data. Functionally, it seems to me to be almost
> the same as functional indexing, but much more transparent, and easier
> to write a query for,
I didn't add the schema; in this case the schema was always the same. If
you had a limited number of schemas you could indicate which one was in
a particular document and use the appropriate decoding.
>> Are you in control of the JSON itself, and are the number of
>> permutations known in advance? It might be that something like table
>> inheritance is a better solution...
>
> Yes, I can alter the db specification. Not sure how table inheritance
> would help, though?
They provide a means where you can refer to the common parts of
disparate schemas in one place, while being able to deal with the inner
details on each child table.
It might not be useful depending on what your goals are. I mentioned it
because I think most people only think of inheritance as "That weird
thing that partitioning uses."
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
