0

I'm currently trying out the "new" data type json. This is part of the definition as php Array (one row of data that is stored in the field)

'processed'=>array(5):
        ["ct"]=>
        int(1)
        ["wt"]=>
        int(11)
        ["cpu"]=>
        int(0)
        ["mu"]=>
        int(1056)
        ["pmu"]=>
        int(0)

I tried the following query:

SELECT id, data->>'processed'>'ct' as sortField FROM system_debug ORDER BY sortField ASC

but all I get in return is an table like this:

id  sortfield
    6   true
    7   true
    8   true
    9   true
    10   true
    11   true
    12   true
    13   true
    14   true
    15   true
    16   true
    17   true
    18   true

I'm trying to implement Sorting without the needs of storing the data seperatly inside the table. Where am I mistaking?

Table Scheme:

                CREATE TABLE system_debug (
                    data JSON,
                    id integer NOT NULL
                );

                CREATE SEQUENCE system_debug_id_seq
                START WITH 1
                INCREMENT BY 1
                NO MINVALUE
                NO MAXVALUE
                CACHE 1;

                ALTER SEQUENCE system_debug_id_seq OWNED BY system_debug.id;

                ALTER TABLE ONLY system_debug ALTER COLUMN id SET DEFAULT nextval(\'system_debug_id_seq\'::regclass);

I appreceate any help that I can get ^^

5
  • Probably a typo: SELECT id, data->>'processed'->'ct' Commented Jun 13, 2015 at 23:31
  • Hmmm that didn't work. Commented Jun 13, 2015 at 23:57
  • ERROR: operator does not exist: text -> unknown LINE 1: SELECT id, data->>'processed'->'ct' FROM system_debug; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Commented Jun 13, 2015 at 23:57
  • SELECT id, data->'processed'->'ct' two typos, sorry ;) Commented Jun 14, 2015 at 0:10
  • Klin can you post your reply as an answer, so I can close this question? Commented Aug 6, 2015 at 21:21

1 Answer 1

1

The expression

data->>'processed'>'ct'

is of type boolean as > is greater than operator. You probably wanted to get

SELECT id, data->'processed'->'ct' ...
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.