Insert an array of json objects ERROR

CrateDB SQL
1

Hi community,
I am quite new and cant make sense of the documentation.
I want to insert an array of JSON objects but I get ERROR Cannot cast expressions from type text to type object_array

insert into prices.latest_prices values ('[{"a":"1"},{"a":"2"}]::array')
returning *;

How to do it? Thank you

2

Should be

insert into prices.latest_prices values (['{"a":"1"}','{"a":"2"}']
returning *;

You need to put the single quotes around the individual objects when using an array column

1 Like
3

Super, that worked!

I added timestamp and business_role, so the table looks like this now:

create table prices.latest_prices (
entry_time timestamp,
business_role text,
products ARRAY(OBJECT(ignored)));

SELECT products
FROM "prices"."latest_prices"
Order by entry_time desc
limit 1;

gets me the latest entry with an array of objects with the same schema. Now from that array I want to select an object with {"id":123}. I cant figure it out in the documentation, neither with select from (select…) statement nor with the array comparisons. Can you give me a hint?

4

SELECT columnname['a'] FROM prices.latestprices

You have to use bracket notation to access properties of an OBJECT

5

Almost got it.

Select t.products['sku']
FROM (SELECT products['product_id'],products['sku'],products['business'],products['retail']
FROM "prices"."latest_prices"
Order by entry_time desc
limit 1) t
Where '100' = Any(t.products['product_id']);

returns an array with ids instead of the object with the product_id 100. How would I do that?

Asking these questions I ask myself how any1 ever got something done before stackoverflow came into existance :smile: Btw, if you got paid hourly support or consultancy, please let me know.

6

You could do something like this

SELECT product['sku'] FROM
( SELECT UNNEST(products) AS product
  FROM prices.latest_prices
  ORDER BY entry_time DESC
) t
WHERE product['product_id'] = '100'
LIMIT 1;

But I am not 100% if it would not be better to change the schema then :grimacing:

Btw, if you got paid hourly support or consultancy, please let me know.

Feel free to contact our sales team (sales@crate.io). Typically it is more in the days range, because of the whole invoicing process. However feel free to ask any questions here :wink:

7

Halleluja, thank you, Sir!
I marked top answer as solution, because it answered the original question

What would be a better schema and where would I learn more about it?