Обсуждение: [MASSMAIL]Extension for PostgreSQL cast jsonb to hstore WIP
Вложения
On 2024-04-02 Tu 07:07, ShadowGhost wrote: > Hello all. > Recently, when working with the hstore and json formats, I came across > the fact that PostgreSQL has a cast of hstore to json, but there is no > reverse cast. I thought it might make it more difficult to work with > these formats. And I decided to make a cast json in the hstore. I used > the built-in jsonb structure to create it and may have introduced > methods to increase efficiency by 25% than converting the form > jsonb->text->hstore. Which of course is a good fact. I also wrote > regression tests to check the performance. I think this extension will > improve the work with jsonb and hstore in PostgreSQL. > If you've read this far, thank you for your interest, and I hope you > enjoy this extension! > One reason we don't have such a cast is that hstore has a flat structure, while json is tree structured, and it's not always an object / hash. Thus it's easy to reliably cast hstore to json but far less easy to cast json to hstore in the general case. What do you propose to do in the case or json consisting of scalars, or arrays, or with nested elements? cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
On 2024-04-02 Tu 07:07, ShadowGhost wrote:
> Hello all.
> Recently, when working with the hstore and json formats, I came across
> the fact that PostgreSQL has a cast of hstore to json, but there is no
> reverse cast. I thought it might make it more difficult to work with
> these formats. And I decided to make a cast json in the hstore. I used
> the built-in jsonb structure to create it and may have introduced
> methods to increase efficiency by 25% than converting the form
> jsonb->text->hstore. Which of course is a good fact. I also wrote
> regression tests to check the performance. I think this extension will
> improve the work with jsonb and hstore in PostgreSQL.
> If you've read this far, thank you for your interest, and I hope you
> enjoy this extension!
>
One reason we don't have such a cast is that hstore has a flat
structure, while json is tree structured, and it's not always an object
/ hash. Thus it's easy to reliably cast hstore to json but far less easy
to cast json to hstore in the general case.
What do you propose to do in the case or json consisting of scalars, or
arrays, or with nested elements?
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
At the moment, this cast supports only these structures, as it was enough for my tasks:{str:numeric}{str:str}{str:bool}{str:null}But it's a great idea and I'll think about implementing it.
Please don't top-post on the PostgreSQL lists. See <https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
I don't think a cast that doesn't cater for all the forms json can take is going to work very well. At the very least you would need to error out in cases you didn't want to cover, and have tests for all of those errors. But the above is only a tiny fraction of those. If the error cases are going to be so much more than the cases that work it seems a bit pointless.
cheers
andrew
-- Andrew Dunstan EDB: https://www.enterprisedb.com
On 2024-04-03 Wn 04:21, Andrew Dunstan
I don't think a cast that doesn't cater for all the forms json can take is going to work very well. At the very least you would need to error out in cases you didn't want to cover, and have tests for all of those errors. But the above is only a tiny fraction of those. If the error cases are going to be so much more than the cases that work it seems a bit pointless.Hi everyone
I changed my mail account to be officially displayed in the correspondence.
I also made an error conclusion if we are given an incorrect value. I
believe that such a cast is needed by PostgreSQL since we already have
several incomplete casts, but they perform their duties well and help in
the right situations.
cheers Antoine Violin
Antoine
On 2024-04-02 Tu 11:43, ShadowGhost wrote:At the moment, this cast supports only these structures, as it was enough for my tasks:{str:numeric}{str:str}{str:bool}{str:null}But it's a great idea and I'll think about implementing it.
Please don't top-post on the PostgreSQL lists. See <https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
I don't think a cast that doesn't cater for all the forms json can take is going to work very well. At the very least you would need to error out in cases you didn't want to cover, and have tests for all of those errors. But the above is only a tiny fraction of those. If the error cases are going to be so much more than the cases that work it seems a bit pointless.
cheers
andrew
-- Andrew Dunstan EDB: https://www.enterprisedb.com
On 2024-04-03 Wn 04:21, Andrew Dunstan
I don't think a cast that doesn't cater for all the forms json can take is going to work very well. At the very least you would need to error out in cases you didn't want to cover, and have tests for all of those errors. But the above is only a tiny fraction of those. If the error cases are going to be so much more than the cases that work it seems a bit pointless.Hi everyone
I changed my mail account to be officially displayed in the correspondence.
I also made an error conclusion if we are given an incorrect value. I
believe that such a cast is needed by PostgreSQL since we already have
several incomplete casts, but they perform their duties well and help in
the right situations.
cheers Antoine Violin
Antoine
On Mon, Jul 15, 2024 at 12:42 PM Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-04-02 Tu 11:43, ShadowGhost wrote:At the moment, this cast supports only these structures, as it was enough for my tasks:{str:numeric}{str:str}{str:bool}{str:null}But it's a great idea and I'll think about implementing it.
Please don't top-post on the PostgreSQL lists. See <https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics>
I don't think a cast that doesn't cater for all the forms json can take is going to work very well. At the very least you would need to error out in cases you didn't want to cover, and have tests for all of those errors. But the above is only a tiny fraction of those. If the error cases are going to be so much more than the cases that work it seems a bit pointless.
cheers
andrew
-- Andrew Dunstan EDB: https://www.enterprisedb.com
5) postgres=# SELECT '{"aaa": "first_value", "aaa": "second_value"}'::jsonb::hstore;
hstore
-----------------------
"aaa"=>"second_value"
(1 row)