CODEWITHSUNDEEP
sqlpostgresqljsonb
ditto
ditto

Reputation: 6277

How to get the value of jsonb data using only the key number id?

I have a jsonb column with the following data:

{"oz": "2835", "cup": "229", "jar": "170"}

I have the key number 0 that represents the first item "oz". How can I pull this value using the 0?

I'm thinking something similar to:

SELECT units->[0] as test

I only have the key ID to reference this data. I do not have the key name "oz".

Upvotes: 0

Views: 41

Answers (1)

jjanes
jjanes

Reputation: 44137

Sounds like a horrible idea. But you can still create a function to implement this horrible idea:

create function jsonb_disaster(jsonb,int) returns jsonb language SQL as $$ 
    select value from jsonb_each($1) with ordinality where ordinality=1+$2
$$;


select jsonb_disaster('{"oz": "2835", "cup": "229", "jar": "170"}',0);
 jsonb_disaster 
----------------
 "2835"

You could also create your own operator to wrap up this disaster:

create operator !> ( function = jsonb_disaster, leftarg=jsonb, rightarg=int);

select '{"cup": "229", "jar": "170", "oz": "2835"}' !> 1;
 ?column? 
----------
 "229"

Upvotes: 1

Related Questions