Re: JSONB spaces in text presentation

On 09/24/2014 12:44 AM, Ilya I. Ashchepkov wrote:
> I'm sorry about sending email several times. I haven't understand, was
> it sent by gmail or not.
>
>
> On Wed, Sep 24, 2014 at 2:30 PM, John R Pierce <pierce(at)hogranch(dot)com
> <mailto:pierce(at)hogranch(dot)com>> wrote:
>
> On 9/24/2014 12:23 AM, Ilya I. Ashchepkov wrote:
>
>
> Is spaces is necessary in text presentation of JSONB?
> In my data resulting text contains ~12% of spaces.
>
>
> can you show us an example of this?
>
>
> One record
> # select data from events.data limit 1;
> {"can": {"lls": {"1": 76.4}, "mhs": 4674.85, "rpm": 168.888, "speed":
> 74, "runned": 166855895, "fuel_consumption": 74213.5}, "crc": 10084,
> "gps": 1, "gsm": {"signal": 100}, "lls": {"1": 733, "2": 717}, "used":
> 19, "speed": 87.4, "valid": 1, "msg_id": 89, "runned": 72.75, "boot_no":
> 256, "digital": {"in": {"1": 1, "2": 0, "3": 0, "4": 0, "5": 0, "6": 0},
> "out": {"1": 0, "2": 0}}, "visible": 20, "ignition": 1, "location":
> {"course": 265, "altitude": 143, "latitude": 55.127888997395836,
> "longitude": 80.8046142578125}, "protocol": 4, "coldstart": 1,
> "timesource": "terminal", "receiver_on": 1, "external_power": 28.07,
> "internal_power": 4.19}
>
> Whitespacis percents in this record:
> # select array_length(regexp_split_to_array(data::text, text ' '),
> 1)*100./length(data::text) from events.data limit 1;
> ?column?
> ---------------------
> 12.3417721518987342
>
> Whitespace in test data
> # select count(*),avg(array_length(regexp_split_to_array(data::text,
> text ' '), 1)*100./length(data::text)) from events.data ;
> count | avg
> --------+---------------------
> 242222 | 12.3649234646118312
>

The only thing I can of is to use json not jsonb. Modified example taken
from docs:

http://www.postgresql.org/docs/9.4/static/datatype-json.html

test=# SELECT '{"bar":"baz","balance":7.77,"active":false}'::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}

test=# SELECT '{"bar":"baz","balance":7.77,"active":false}'::json;
json
---------------------------------------------
{"bar":"baz","balance":7.77,"active":false}

json will return exactly what was put in.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com