How to insert an Array that contains Objects into one column

Question

I have an existing database that has an important column that's called InDays with nvarchar(150) datatype.

In the existing data there's an Array that has an Object inside and looks like that:

InDays
----------------------------------------------------------------------------------------------
[{ "day": 1, "from": "12:00am", "to": "2:00am"},{ "day": 4, "from": "2:00am", "to": "4:00am"}]

The Objects inside can be more than one.

I tried inserting it as it is, but i get [object Object] instead of the value.

EDIT-- The insert code.

DECLARE @InDays nvarchar(150) = [{ "day": 1, "from": "12:00am", "to": "2:00am"},{ "day": 4, "from": "2:00am", "to": "4:00am"}]

INSERT INTO Course (
        InDays
    )
    VALUES
    (
        @InDays
    )

can you please show the insert code ?

mukesh kudi
– mukesh kudi

2018-08-31 21:48:25 +00:00
Commented Aug 31, 2018 at 21:48 — mukesh kudi
– mukesh kudi, Commented Aug 31, 2018 at 21:48
I edited the post.

ANUBIS
– ANUBIS

2018-08-31 21:57:13 +00:00
Commented Aug 31, 2018 at 21:57 — ANUBIS
– ANUBIS, Commented Aug 31, 2018 at 21:57

Joel Coehoorn · Accepted Answer · 2018-08-31 22:01:32Z

1

I have ... an important column that's ... nvarchar(150) datatype.

So use that type with your insert:

DECLARE @InDays nvarchar(150) = '[{ "day": 1, "from": "12:00am", "to": "2:00am"},{ "day": 4, "from": "2:00am", "to": "4:00am"}]'

Though I have my doubts 150 will be large enough if you could end up with many of these. Stepping through just the first object, assuming it's typical, you'll run out of space already at just the 5th member of the array.

answered Aug 31, 2018 at 22:01

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ANUBIS Over a year ago

I obviously need to map through this object later. will that be possible?

Joel Coehoorn Over a year ago

Possible, yes. But not at all simple unless you can use the new json types.

ANUBIS Over a year ago

Could you provide an example of this?

Joel Coehoorn Over a year ago

Nope. My personal feeling is needing json in the db is poor schema design, and so I've managed to avoid doing it myself so far.

Joel Coehoorn Over a year ago

Lots of people really like the json features. I'm just not one of them. Oh, and I should amend it to "needing queryable json". There are lots of good reasons to store json snippets in a database (CRMs, service logs, caches, etc). It's when you start using them as a crutch to avoid laying out real tables I get all snobby about it.

|

ANUBIS · Accepted Answer · 2018-09-04 13:46:52Z

0

For future explorers

I simply had to convert the JSON format into a String.

Which is done by JSON.stringify([{"value": value}]) then directly store it into the sql database

Then JSON.parse("value") to convert it to JSON again.

edited Sep 4, 2018 at 13:46

answered Sep 1, 2018 at 6:58

ANUBIS

7361 gold badge9 silver badges20 bronze badges

Collectives™ on Stack Overflow

How to insert an Array that contains Objects into one column

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related