# Insert an array of json objects ERROR

**URL:** <https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819>\
**Category:** SQL\
**Created:** [September 21, 2021, 10:52pm UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819 "2021-09-21T22:52:12Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sven](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Sven](https://community.cratedb.com/u/Sven)\
**Post date:** [September 21, 2021, 10:52pm UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/1 "2021-09-21T22:52:12Z")

</div>

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`

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

```

How to do it? Thank you

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 22, 2021, 9:59am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/2 "2021-09-22T09:59:36Z")

</div>

> [@Sven](#):
>
> ```auto
> insert into prices.latest_prices values ('[{"a":"1"},{"a":"2"}]::array')
> returning *;
> 
> ```

Should be

```SQL
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

---

<div class="post-metadata">

**Author:** ![Sven](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Sven](https://community.cratedb.com/u/Sven)\
**Post date:** [September 22, 2021, 4:25pm UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/3 "2021-09-22T16:25:38Z")

</div>

Super, that worked!

I added timestamp and business\_role, so the table looks like this now:

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

```

```sql
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?

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 23, 2021, 9:48am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/4 "2021-09-23T09:48:33Z")

</div>

`SELECT columnname['a'] FROM prices.latestprices`

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

---

<div class="post-metadata">

**Author:** ![Sven](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Sven](https://community.cratedb.com/u/Sven)\
**Post date:** [September 23, 2021, 10:13am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/5 "2021-09-23T10:13:30Z")

</div>

Almost got it.

```sql
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 😄 Btw, if you got paid hourly support or consultancy, please let me know.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [September 23, 2021, 11:06am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/6 "2021-09-23T11:06:17Z")

</div>

> [@Sven](#):
>
> `entry_time`

You could do something like this

```sql
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 😬

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

Feel free to contact our sales team ([sales@crate.io](mailto: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 😉

---

<div class="post-metadata">

**Author:** ![Sven](https://avatars.discourse-cdn.com/v4/letter/s/e47c2d/32.png) [@Sven](https://community.cratedb.com/u/Sven)\
**Post date:** [September 23, 2021, 11:52am UTC](https://community.cratedb.com/t/insert-an-array-of-json-objects-error/819/7 "2021-09-23T11:52:04Z")

</div>

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?