Sfrgttk

How to limit sight distance to 1 km

Are there any modern advantages of a fire piston?

If I delete my router's history can my ISP still provide it to my parents?

Could a phylactery of a lich be a mirror or does it have to be a box?

Would a National Army of mercenaries be a feasible idea?

Writing a character who is going through a civilizing process without overdoing it?

Does SQL Server 2017, including older versions, support 8k disk sector sizes?

Why Prushim were the ones who "separated"?

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Finding a mistake using Mayer-Vietoris

Early credit roll before the end of the film

Cookies - Should the toggles be on?

Measure of a Brownian motion = normal distribution?

How to prevent cleaner from hanging my lock screen in Ubuntu 16.04

Does paint affect EMI ability of enclosure?

Am I a Rude Number?

Can we use the stored gravitational potential energy of a building to produce power?

Why would space fleets be aligned?

Is my visa status for all destinations in a flight with connections checked in the beginning or before each flight?

Why did the villain in the first Men in Black movie care about Earth's Cockroaches?

My cat mixes up the floors in my building. How can I help him?

Advice for a new journal editor

Blindfold battle as a gladiatorial spectacle - what are the tactics and communication methods?

Why do no American passenger airlines still operate dedicated cargo flights?

PostgreSQL select jsonb key value from an array while joining table

MySQL : LEFT JOIN not fetching required dataPostgreSQL joining using JSONBPostgreSql JSONB SELECT against multiple valuesHow to get particular object from jsonb array in PostgreSQL?How to return value for key from a jsonb column?postgresql: how to define a JSONB column with default valuePostgreSQL 9.5 query performance depends on JOINed column in SELECT clausePostgreSQL JSONB update array elements queryFilter rows by objects in jsonb array in PostgreSQLJSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)

0

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

tables:

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

example of join_table.arr

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

What I am trying to achieve is to get concat a comma separated string of all the join_table.arr Name values

postgresql join json

share

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

   SELECT

        t.id,

        concat(t.content, array_to_string(jsonb_array_elements(j.arr)->'Name'->>0, ',')) AS content

    FROM

        main_table t

    LEFT JOIN join_table j ON t.j_id = t.id

    WHERE 

        t.id IN (1,2,3,4,...)

CREATE TABLE main_table (

        id bigserial PRIMARY KEY,

        j_id BIGINT,

        content text

);



CREATE TABLE join_table (

        id BIGSERIAL PRIMARY KEY,

        arr JSONB

);

[

  {

    "Id": 1,

    "Name": "name1",

  },

  {

    "Id": 2,

    "Name": "name2",

  }

]

share

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

gadelkareemgadelkareem

1011

New contributor

gadelkareem is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 4 mins ago

gadelkareemgadelkareem

1011