User demand, and idea, for C-code conversions from JSON arrays to PostgreSQL arrays - Mailing list pgsql-hackers
From | David G. Johnston |
---|---|
Subject | User demand, and idea, for C-code conversions from JSON arrays to PostgreSQL arrays |
Date | |
Msg-id | CAKFQuwZSLjLtnWOWfxb0B3D5rRX=60OCuHAELYqJA2dHgBfogQ@mail.gmail.com Whole thread Raw |
List | pgsql-hackers |
<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">All,</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Ovenin the "JSON[B] arrays are second-class citizens" thread [1] I made theobservation that the only way to get a PostgreSQL array from a JSON array is via the "elements->cast->array_agg"chain. For JSON arrays that are homogeneous in nature the capability to go "directly" fromJSON to json[], text[], bigint[], etc... seems like it would have some value. </div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Acouple, so far, of community members have chimed in. Maybe this ends upon a ToDo somewhere but for the moment I figured I'd float the idea on its own thread since the other one is really abouta different (though somewhat related) topic.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Inthe spirit of the json_populate_record like functions something with thefollowing signature seems usable:</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif">as_array(anyarray, jsonb) : anyarray [2]</div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif">so that actual calls look like:</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">SELECTas_array(null::text[], '["a", "b", "c"]'::jsonb)</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Forbetter or worse while every table gets a corresponding composite type explicitlycreated types cannot be treated as row types in this situation</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">SELECTjsonb_populate_record(null::text[], '["a", "b", "c"]'::jsonb)</div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif">> ERROR: first argument ofjsonb_populate_record must be a row type</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br/></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">Looseningthe restriction and allowing jsonb_populate_record to fulfill therole of the above described "as_array" function would be something to consider, but likely not possible or worth any effortin trying to make happen.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><divclass="gmail_default" style="font-family:arial,helvetica,sans-serif">David J.</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">[1] <a href="https://www.postgresql.org/message-id/CADkLM=fSC+otuBmzoJT6Riyksue3HpHgu2=Mofcv=fd0derhGg@mail.gmail.com">https://www.postgresql.org/message-id/CADkLM=fSC+otuBmzoJT6Riyksue3HpHgu2=Mofcv=fd0derhGg@mail.gmail.com</a></div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">[2]ROTQ[3] - whose idea was it to put the type first and the data second?<br/></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br /></div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif">[3] Rhetorical Off-Topic Question</div><div class="gmail_default"style="font-family:arial,helvetica,sans-serif"><br /></div></div>
pgsql-hackers by date: