Question 89217 in PostgreSQL Question posted in
The official documentation can be found here.

Query a JSON with integers as keys in PostgreSql

I have data objects that are in the format

{
  "1": [
    {
      "2": 17
    }
  ],
  "3": 126
}

and stored in PostgreSql database in a column with the type json.
How can I query these, I’ve tried:

json_each(sc.data::json)

returns ERROR: cannot deconstruct a scalar

If I try

sc.data::json->'1'

it returns null.

Ideally, I would like to get some fields from the "1" array.

enter image description here

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks to the effort from @SelVazi and @Bergi, I have realized that my data was wrong. It was a common escaped string instead of a JSON object.

    The query to fix it is

    UPDATE my_table SET data = (data #>> '{}')::json

    After doing this all of the queries start working as expected, f.x.

    SELECT data->'1' from my_table

  2. 0

    The error means that in your data column there maybe one or more Simple scalar/primitive value they can be numbers, quoted strings, true, false, or null.

    I have reproduced your error :

    create table mytable (
  data json
);

insert into mytable values ('{
  "1": [
    {
      "2": 17
    }
  ],
  "3": 126
}');

select key, value
from mytable, json_each(data)

    Result OK :

    key value
1   [{"2": 17}]
3   126

    But when add one scalar value not null I got error

    ERROR: cannot deconstruct a scalar

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search