How to store a json value in a postgres column

Question

I have a table with two columns,One column should store int and other should store json.

here the data which i want to store in the table.

id,polygon
1,"{""type"": ""Feature"",
    ""properties"": {
        ""stroke"": ""#555555"",
        ""stroke-width"": 2,
        ""stroke-opacity"": 1,
        ""fill"": ""#00aa22"",
        ""fill-opacity"": 0.5
    },
    ""geometry"": {
        ""type"": ""Polygon"",
        ""coordinates"": [
            [
                [-76.97021484375,
                    40.17887331434696
                ],
                [-74.02587890625,
                    39.842286020743394
                ],
                [-73.4326171875,
                    41.713930073371294
                ],
                [-76.79443359375,
                    41.94314874732696
                ],
                [-76.97021484375,
                    40.17887331434696
                ]
            ]
        ]
    }
}"

I tired storing in postgres as follows:

insert into gjl_polygon values(1,'"{""type"": 
""Feature"",""properties"": {""stroke"": ""#555555"",""stroke- 
width"": 2,""stroke-opacity"": 1,""fill"": ""#00aa22"",""fill- 
opacity"": 0.5},""geometry"": {""type"": 
""Polygon"",""coordinates"": 
[[[-76.97021484375,40.17887331434696],[-74.02587890625, 
39.842286020743394 ],[-73.4326171875, 41.713930073371294], 
[-76.79443359375,41.94314874732696], 
[-76.97021484375,40.17887331434696]]]}}"');

I got the following error,

Expecting ':' delimiter: line 1 column 4 (char 3)

It looks like you have a string wrapped in a string '"{""type"": ""Feature"",... this should probably look more like '{"type":"feature",... without the extra double quotes " — phuzi
– phuzi, Commented Jul 18, 2019 at 7:40
Trying to remove extra double quotes get me the following error Expecting ':' delimiter: line 1 column 4 (char 3) — ishika
– ishika, Commented Jul 18, 2019 at 9:06
Is that an actual query you are executing? Including new lines in places that shouldn't have them? Did you remove double quotes from start and end of your string (those are not double) — Łukasz Kamiński
– Łukasz Kamiński, Commented Jul 18, 2019 at 9:47

Wolfetto · Accepted Answer · 2019-07-18 07:52:41Z

The problem of your code is the use of double quotes twice. Try to edit like this:

{
    "type": "Feature",
    "properties": {
        "stroke": "#555555",
        "stroke-width": 2,
        "stroke-opacity": 1,
        "fill": "#00aa22",
        "fill-opacity": 0.5
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [
            [
                [-76.97021484375,
                    40.17887331434696
                ],
                [-74.02587890625,
                    39.842286020743394
                ],
                [-73.4326171875,
                    41.713930073371294
                ],
                [-76.79443359375,
                    41.94314874732696
                ],
                [-76.97021484375,
                    40.17887331434696
                ]
            ]
        ]
    }
}

The JSON above is a valid JSON string and it should work as expected.

Collectives™ on Stack Overflow

How to store a json value in a postgres column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related