Re: is JSON really "a type" (Re: data to json enhancements) - Mailing list pgsql-hackers

<p class="MsoNormal"><p class="MsoNormal">Datum_to_json<p class="MsoNormal"><br /><p class="MsoNormal"><br /><p
class="MsoNormal">SELECT'a=>1'::hstore, '<a>1</a>'::xml, '{"a":1}'<p class="MsoNormal"><br /><p
class="MsoNormal">(Pleasenote that last column is unknown – datatype)<p class="MsoNormal"><p class="MsoNormal"><br /><p
class="MsoNormal"><br/><p class="MsoNormal">Now, what is the main goal? to get:<p class="MsoNormal"><p
class="MsoListParagraphCxSpMiddle">1) <pclass="MsoListParagraphCxSpMiddle"><p class="MsoListParagraphCxSpMiddle">{<p
class="MsoListParagraphCxSpMiddle">   "hstore": "\"a\"=>\"1\"",<p class="MsoListParagraphCxSpMiddle">    "xml":
"<a>1</a>",<pclass="MsoListParagraphCxSpMiddle">    "?column?": "{\"a\":1}"<p
class="MsoListParagraphCxSpMiddle">}<p class="MsoListParagraphCxSpMiddle"><br /><p
class="MsoListParagraphCxSpMiddle">or:<pclass="MsoListParagraphCxSpMiddle">2)<p class="MsoListParagraphCxSpMiddle">{<p
class="MsoListParagraphCxSpMiddle"><pclass="MsoListParagraphCxSpMiddle">    "hstore": {<p
class="MsoListParagraphCxSpMiddle">       "a": "1"<p class="MsoListParagraphCxSpMiddle">    },<p
class="MsoListParagraphCxSpMiddle">    "xml": {<p class="MsoListParagraphCxSpMiddle">        "a": "1"<p
class="MsoListParagraphCxSpMiddle">   },<p class="MsoListParagraphCxSpMiddle">    "?column?": {<p
class="MsoListParagraphCxSpMiddle">       "a": 1<p class="MsoListParagraphCxSpMiddle">    }<p
class="MsoListParagraphCxSpMiddle">}<p><pclass="MsoListParagraphCxSpMiddle"><br /><p><p
class="MsoListParagraphCxSpMiddle">1)  is already possible to get now:<p class="MsoListParagraphCxSpMiddle"> <p
class="MsoListParagraphCxSpMiddle">SELECTrow_to_json(t) FROM (SELECT 'a=>1'::hstore, '<a>1</a>'::xml,
'{"a":1}')AS t<p class="MsoListParagraphCxSpMiddle"> <p class="MsoListParagraphCxSpMiddle">I don’t know how things work
underthe hood (haven’t taken a look on row_to_json source…) But it says to me that there is already Datum_to_json –
somewhere…<pclass="MsoListParagraphCxSpMiddle"><br /><p class="MsoListParagraphCxSpMiddle">2) Is not possible atm...
butwould be if we have CAST functions for each DataType - I am not sure is it possible to write some generic function
whatwill convert any datype to JSON (or to an "intermediate" dynamic datatype) without knowing specific things about
concreteDataType (though I dont see big difference will type itself provide _to_json or to_dynamic_type function)...<p
class="MsoListParagraphCxSpMiddle"><br/><p class="MsoListParagraphCxSpMiddle"><br /><p
class="MsoListParagraphCxSpMiddle">IsJSON really a type?<p class="MsoListParagraphCxSpMiddle"><br /><p
class="MsoListParagraphCxSpMiddle">I think it is... But just needs better handling... We have atm type JSON - though we
can'tsay<p class="MsoListParagraphCxSpMiddle"><br /><p class="MsoListParagraphCxSpMiddle">SELECT * FROM foo WHERE
json_member("member_name",json_column) = 1;<p class="MsoListParagraphCxSpMiddle">I mean - we can't without plv8... to
bemore precise... We have used to use plv8 - but it has became a bit slow how table grows... so we have made some
workarounds...with custom functions what improves response for above query... however it is very customised... but
maybeit could give some ideas for indexing JSON...<p class="MsoListParagraphCxSpMiddle"><br /><p
class="MsoListParagraphCxSpMiddle">basically...ifwe have table:<p class="MsoListParagraphCxSpMiddle">foo (id PK, some
columns,json_column)<p class="MsoListParagraphCxSpMiddle"> we have made another table:<p
class="MsoListParagraphCxSpMiddle">index_json(idint, member_name ltree, json_value text) table with index (member_name,
json_value)<pclass="MsoListParagraphCxSpMiddle">when we instert row in foo we also json_column value i.e. example from
desiredresult 2) above transfer to<p class="MsoListParagraphCxSpMiddle">1, 'hstore.a', '"1"'<p
class="MsoListParagraphCxSpMiddle">1,'xml.a', '"1"'<p class="MsoListParagraphCxSpMiddle">1, '?column?.a', '1'<p
class="MsoListParagraphCxSpMiddle">andnow when we want result for  SELECT * FROM foo WHERE json_member("xml.a",
json_column)= 1;<p class="MsoListParagraphCxSpMiddle">we actually asks SELECT id FROM index_json WHERE json_member =
$json_memberand json_value = $json_value into my_ids<p class="MsoListParagraphCxSpMiddle">and then SELECT * FROM foo
WHEREid = ANY(my_ids)<p class="MsoListParagraphCxSpMiddle"><br /><p class="MsoListParagraphCxSpMiddle">Also, to get
SELECT* FROM foo as one JSON - atm, I think query is a monster (without plv8), best would be if it is possible to
providesome shortcut support...<p class="MsoListParagraphCxSpMiddle"><br /><p class="MsoListParagraphCxSpMiddle">Kind
regards,<pclass="MsoListParagraphCxSpMiddle">Misa<p>