Datum_to_json
SELECT 'a=>1'::hstore, '<a>1</a>'::xml, '{"a":1}'
(Please note that last column is unknown – datatype)
Now, what is the main goal? to get:
1)
{
"hstore": "\"a\"=>\"1\"",
"xml": "<a>1</a>",
"?column?": "{\"a\":1}"
}
or:
2)
{
"hstore": {
"a": "1"
},
"xml": {
"a": "1"
},
"?column?": {
"a": 1
}
}
1) is already possible to get now:
SELECT row_to_json(t) FROM (SELECT 'a=>1'::hstore, '<a>1</a>'::xml,
'{"a":1}') AS t
I don’t know how things work under the hood (haven’t taken a look on
row_to_json source…) But it says to me that there is already Datum_to_json
– somewhere…
2) Is not possible atm... but would be if we have CAST functions for each
DataType - I am not sure is it possible to write some generic function what
will convert any datype to JSON (or to an "intermediate" dynamic datatype)
without knowing specific things about concrete DataType (though I dont see
big difference will type itself provide _to_json or to_dynamic_type
function)...
Is JSON really a type?
I think it is... But just needs better handling... We have atm type JSON -
though we can't say
SELECT * FROM foo WHERE json_member("member_name", json_column) = 1;
I mean - we can't without plv8... to be more 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 maybe it could give some
ideas for indexing JSON...
basically...if we have table:
foo (id PK, some columns, json_column)
we have made another table:
index_json(id int, member_name ltree, json_value text) table with index
(member_name, json_value)
when we instert row in foo we also json_column value i.e. example from
desired result 2) above transfer to
1, 'hstore.a', '"1"'
1, 'xml.a', '"1"'
1, '?column?.a', '1'
and now when we want result for SELECT * FROM foo WHERE
json_member("xml.a", json_column) = 1;
we actually asks SELECT id FROM index_json WHERE json_member = $json_member
and json_value = $json_value into my_ids
and then SELECT * FROM foo WHERE id = ANY(my_ids)
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 provide some
shortcut support...
Kind regards,
Misa
